聚合，整理和将行转换成列

本文介绍了聚合，整理和将行转换成列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有下表 Id Letter 1001 A 1001 H 1001 H 1001 H 1001 B 1001 H 1001 H 1001 H 1001 H 1001 H 1001 H 1001 A 1001 H 1001 H 1001 H 1001 B 1001 A 1001 H 1001 H 1001 H 1001 B 1001 B 1001 H 1001 H 1001 H 1001 B 1001 H 1001 A 1001 G 1001 H 1001 H 1001 A 1001 B 1002 B 1002 H 1002 H 1002 B 1002 G 1002 H 1002 B 1002 G 1002 G 1002 H 1002 B 1002 G 1002 H 1002 H 1002 G 1002 H 1002 H 1002 H 1002 H 1002 H 1002 M 1002 N 1002 G 1002 H 1002 H 1002 M 1002 M 1002 A 1002 H 1002 H 1002 H 1002 A 1002 B 1002 B 1002 H 1002 H 1002 H 1002 B 1002 H 1002 H 1002 H 1002 A 1002 A 1002 A 1002 H 1002 H 1002 H 1002 H 1002 B 1002 H 1003 G 1003 H 1003 H 1003 N 1003 M 我正在尝试将其转置为使第一列中的每个不同的ID和第二列中的所有字母在原始表格中的每个空白行都有一个空格： 1001 AHHH BHHH HHH AHHHB AHHHB BHHHB H AGHHAB 1002 BHHB GH BGGH BGHH GHH HHHMN GHHMM AHHHAB BHHH BHHHAA AHHHHB H 1003 GHHNM 我有大约100个不同的ID。我尝试使用TRANSPOSE和TRIM的公式。我也试过一个宏，VLOOKUP似乎是最简单的方法，但是找不到如何解决方案你不能连接一个使用原始工作表函数的单元格范围（也称为 Letters ）而不事先知道范围。由于您将字符串集合到组中具有随机数元素，因此VBA循环方法似乎是解决问题的最佳方法（如果不是唯一的）。循环可以确定工作表函数根本无法执行。 点击 + + ， + Q 返回到您的工作表。使用A1中的 Id 开始的活动工作表上的样本数据，点击 + 打开宏对话框和运行宏。 来自concatenate_and_transpose_to_delim_string的结果： 来自concatenate_and_transpose_into_columns的结果： 结果将写入从D2开始的单元格。可能是最好的，如果没有什么重要的事先被覆盖。 附录： 我原来错误地解释了您的请求，并将字符串组拆分为单独的列。我已经纠正了一个补充程序，更紧密地遵循您的要求描述，但保留两个变体供其他人参考。 I have the following table Id Letter1001 A1001 H1001 H1001 H1001 B1001 H1001 H1001 H1001 H1001 H1001 H1001 A1001 H1001 H1001 H1001 B1001 A1001 H1001 H1001 H1001 B1001 B1001 H1001 H1001 H1001 B1001 H1001 A1001 G1001 H1001 H1001 A1001 B1002 B1002 H1002 H1002 B1002 G1002 H1002 B1002 G1002 G1002 H1002 B1002 G1002 H1002 H1002 G1002 H1002 H1002 H1002 H1002 H1002 M1002 N1002 G1002 H1002 H1002 M1002 M1002 A1002 H1002 H1002 H1002 A1002 B1002 B1002 H1002 H1002 H1002 B1002 H1002 H1002 H1002 A1002 A1002 A1002 H1002 H1002 H1002 H1002 B1002 H1003 G1003 H1003 H1003 N1003 MAnd I'm trying to transpose it to make each different id in the first column and all the letters in the second column with one blank space for each blank row in the original table:1001 AHHH BHHH HHH AHHHB AHHHB BHHHB H AGHHAB1002 BHHB GH BGGH BGHH GHH HHHMN GHHMM AHHHAB BHHH BHHHAA AHHHHB H1003 GHHNMI have about 100 different id. I tried to do with a formula using TRANSPOSE and TRIM. I also tried with a macro and VLOOKUP seems to be the easiest way but can't find out how 解决方案 You cannot concatenate a range of cells (aka Letters) using native worksheet functions without knowing the scope beforehand. As your collection of strings into groups has random numbers of elements, a VBA loop approach seems the best (if not the only) way to address the issue. The loop can make determinations along the way that a worksheet function is simply incapable of performing.Tap + and when the Visual Basic Editor (aka VBE) opens, immediately use the pull-down menus to Insert ► Module (+,). Paste one or both of the following into the new pane titled something like Book1 - Module1 (Code).To concatenate the string groups delimited by a space:Sub concatenate_and_transpose_to_delim_string() Dim rw As Long, lr As Long, pid As Long, str As String Dim bPutInColumns As Boolean With ActiveSheet lr = .Cells(Rows.Count, 1).End(xlUp).row .Cells(1, 4).Resize(1, 2) = Array("Id", "Letters") pid = .Cells(2, 1).Value For rw = 2 To lr If IsEmpty(.Cells(rw, 1)) Then str = str & Chr(32) If pid <> .Cells(rw + 1, 1).Value Then .Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) = pid .Cells(Rows.Count, 4).End(xlUp).Offset(0, 1) = str End If ElseIf pid <> .Cells(rw, 1).Value Then pid = .Cells(rw, 1).Value str = .Cells(rw, 2).Value Else str = str & .Cells(rw, 2).Value End If Next rw .Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) = pid .Cells(Rows.Count, 4).End(xlUp).Offset(0, 1) = str End WithEnd SubTo split the string groups into columns:Sub concatenate_and_transpose_into_columns() Dim rw As Long, lr As Long, nr As Long, pid As Long, str As String With ActiveSheet lr = .Cells(Rows.Count, 1).End(xlUp).row .Cells(1, 4).Resize(1, 2) = Array("Id", "Letters") For rw = 2 To lr If IsEmpty(.Cells(rw, 1)) Then .Cells(nr, Columns.Count).End(xlToLeft).Offset(0, 1) = str str = vbNullString ElseIf pid <> .Cells(rw, 1).Value Then pid = .Cells(rw, 1).Value nr = .Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).row .Cells(nr, 4) = pid str = .Cells(rw, 2).Value Else str = str & .Cells(rw, 2).Value End If Next rw .Cells(nr, Columns.Count).End(xlToLeft).Offset(0, 1) = str End WithEnd SubTap + to return to your worksheet. With your sample data on the active worksheet starting with Id in A1, tap + to open the Macros dialog and Run the macro.Results from concatenate_and_transpose_to_delim_string:    Results from concatenate_and_transpose_into_columns:    The results will be written into the cells starting at D2. Probably best if there was nothing important there beforehand that would be overwritten.Addendum:I original misinterpreted your request and split the string groups into separate columns. I've rectified that with a supplemental routine that more closely follows your description of requirements but kept both variations for others to reference. 这篇关于聚合，整理和将行转换成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！