Как перенести ячейки в один столбец на основе уникальных значений в другом столбце?
Предположим, у вас есть диапазон данных, содержащий два столбца, теперь вы хотите транспонировать ячейки в одном столбце в горизонтальные строки на основе уникальных значений в другом столбце, чтобы получить следующий результат. У вас есть какие-нибудь хорошие идеи, как решить эту проблему в Excel?
Перенести ячейки в один столбец на основе уникальных значений с помощью формул
Перенести ячейки в один столбец на основе уникальных значений с кодом VBA
Перенести ячейки в один столбец на основе уникальных значений с помощью Kutools for Excel
Перенести ячейки в один столбец на основе уникальных значений с помощью формул
С помощью следующих формул массива вы можете извлечь уникальные значения и транспонировать соответствующие данные в горизонтальные строки, сделайте следующее:
1. Введите эту формулу массива: = ИНДЕКС ($ A $ 2: $ A $ 16, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) в пустую ячейку, например D2, и нажмите Shift + Ctrl + Ввод вместе, чтобы получить правильный результат, см. снимок экрана:
Внимание: В приведенной выше формуле 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 + Ввод ключи, чтобы получить результат, см. снимок экрана:
Внимание: В формуле выше: B2: B16 это данные столбца, которые вы хотите транспонировать, A2: A16 столбец, значения которого вы хотите транспонировать, и D2 содержит уникальное значение, которое вы извлекли на шаге 1.
4. Затем перетащите дескриптор заполнения справа от ячеек, в которых вы хотите отобразить транспонированные данные, пока не отобразится 0, см. Снимок экрана:
5. А затем продолжайте перетаскивать дескриптор заполнения в диапазон ячеек, чтобы получить транспонированные данные, как показано на следующем снимке экрана:
Перенести ячейки в один столбец на основе уникальных значений с кодом VBA
Возможно, формулы сложны для понимания, здесь вы можете запустить следующий код VBA, чтобы получить желаемый результат.
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
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 Sub
3. Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно с напоминанием о выборе диапазона данных, который вы хотите использовать, см. снимок экрана:
4, Затем нажмите OK При нажатии кнопки появится другое окно с напоминанием о том, что нужно выбрать ячейку для отображения результата, см. снимок экрана:
6. Нажмите OK кнопку, и данные в столбце B были перенесены на основе уникальных значений в столбце A, см. снимок экрана:
Перенести ячейки в один столбец на основе уникальных значений с помощью Kutools for Excel
Если вы только что Kutools for Excel, объединяя Расширенные ряды комбинирования и Разделить клетки утилит, вы можете быстро выполнить эту задачу без каких-либо формул или кода.
Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней. |
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите диапазон данных, который вы хотите использовать. (Если вы хотите сохранить исходные данные, сначала скопируйте и вставьте данные в другое место.)
2. Затем нажмите Кутулс > Слияние и разделение > Расширенные ряды комбинирования, см. снимок экрана:
3. В Объединить строки на основе столбца диалоговом окне выполните следующие действия:
(1.) Щелкните имя столбца, на основе которого вы хотите транспонировать данные, и выберите Основной ключ;
(2.) Щелкните другой столбец, который нужно транспонировать, и щелкните Сочетать затем выберите один разделитель для разделения объединенных данных, например пробел, запятую, точку с запятой.
4. Затем нажмите Ok Кнопка, данные в столбце B были объединены в одну ячейку на основе столбца A, см. снимок экрана:
5. Затем выберите объединенные ячейки и нажмите Кутулс > Слияние и разделение > Разделить клетки, см. снимок экрана:
6. В Разделить клетки диалоговое окно, выберите Разделить на столбцы под Тип вариант, а затем выберите разделитель, который разделяет ваши объединенные данные, см. снимок экрана:
7. Затем нажмите Ok и выберите ячейку, чтобы отобразить результат разделения в появившемся диалоговом окне, см. снимок экрана:
8. Нажмите OK, и вы получите нужный вам результат. Смотрите скриншот:
Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!
Демонстрация: транспонируйте ячейки в один столбец на основе уникальных значений с помощью Kutools for Excel
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!