汇总，整理和转置行转换成列

本文介绍了汇总，整理和转置行转换成列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有以下表 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 M和我想调换它做的第一列中的每个不同的ID，并与原表中的每个空白行一个空格第二列所有字母：And 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 GHHNM我有大约100个​​不同的ID。我试着用移调和TRIM一个公式来做到。我也试图与宏观和VLOOKUP似乎是最简单的方法，但不能找出如何I 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推荐答案您不能连接单元格区域（又名字母的）使用本机工作表函数不知道事先的范围。作为您的字符串集合成组有元素随机数，一个VBA循环的方法似乎是最好的（如果不是唯一）的方式来解决这个问题。该循环可以作出裁定沿着工作表函数就是不能执行的方式。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.点击替代 + F11 和Visual Basic编辑器（又名VBE）打开时，立即使用下拉菜单插入►模块（替代 + I ， M ）。以下内容粘贴到名为类似的新窗格中的一个或两个的第一册 - 模块1（code）的Tap Alt+F11 and when the Visual Basic Editor (aka VBE) opens, immediately use the pull-down menus to Insert ► Module (Alt+I,M). Paste one or both of the following into the new pane titled something like Book1 - Module1 (Code). 要连接用空格分隔字符串组： 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 Sub 到字符串组分成列： 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 Sub点击替代 + 问：返回到您的工作表。与编号在A1开始活动工作表上的样本数据，请点击替代 + F8 打开宏对话框和运行宏。Tap Alt+Q to return to your worksheet. With your sample data on the active worksheet starting with Id in A1, tap Alt+F8 to open the Macros dialog and Run the macro. 从concatenate_and_transpose_to_delim_string结果：      从concatenate_and_transpose_into_columns结果：     的结果将被写入起始于D2中的细胞。也许最好的，如果没有什么重要的事先存在，将被覆盖。The results will be written into the cells starting at D2. Probably best if there was nothing important there beforehand that would be overwritten. 附录： 我原来misinter preTED您的要求和串组分割成单独列。我纠正，与更紧密地跟随你的需求描述的补充例行但保留对别人都变化参考一下。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. 这篇关于汇总，整理和转置行转换成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！