在Excel中处理数据时，通常会合并单元格、列和行以组合数字或文本。
您可能需要在Excel(Excel)中合并信息的原因有多种。有时是为了改进格式。其他时候是将来自多个单元格的信息带入一个单元格。
无论出于何种原因，您都可以使用以下方法以任何您需要的方式在Excel中组合信息。(Excel)
Excel中如何合并单元格
合并多个单元格的最常见原因之一是在Excel电子表格中创建标题行时。
- 您可以在要合并的任何单元格内创建标题文本。将文本格式化(Format)为您想要的显示方式，包括字体大小、样式和垂直对齐或高度(vertical alignment or height)。
- 接下来，您需要确保文本在数据表的宽度上完美对齐。为此，请选择要合并和居中的所有单元格。
- 选择要合并的所有单元格后，在主(Home)菜单上选择Merge & Center。这会将所有选定的单元格合并到一个单元格中，并将该单元格中的文本或数据居中。
您也可以对垂直单元格执行相同的操作。例如，如果您想让月份名称在该月份所有行的右侧垂直居中，您可以使用Merge Cells来做到这一点。
去做这个：
- 在第一个单元格中输入月份的名称。
- 接下来，选择所有相关的单元格。所以在这种情况下，一月(January)将包括第一个和第二个单元格。
- 现在，选择这些单元格后，在Home菜单中选择(Home)Merge & Center旁边的下拉箭头。从下拉菜单中选择合并单元格。(Merge Cells)
这会将Excel中的这些单元格合并为一个，并将文本默认放置在单元格的底部。在这种情况下您不想使用Merge & Center的原因是因为这将使文本在列中水平居中。
在这种情况下，您可能更喜欢将文本垂直居中，以便它位于它所应用的所有单元格的中心。为此，只需选择新合并的单元格，然后在对齐(Alignment)功能区组的主(Home)菜单中选择垂直居中对齐图标。
这会将文本与所有相关单元格垂直对齐，以便所有内容都完美排列。
如何在 Excel 中合并列
在Excel 中(Excel)，您不仅限于合并单个单元格。您还可以合并整列数据。
例如，在此示例电子表格中，您可能希望将销售助理的名字(First Name)和姓氏组合到另一列作为他们的全名。
在此电子表格中，如果您选择两列中的所有数据并尝试使用Merge & Center合并单元格，您将看到一条错误消息，指出您将丢失除第一个单元格之外的所有单元格中的数据范围。
这根本没用。
有几种快速方法可以组合两列中的所有数据而不会丢失任何内容。
使用记事本合并列
将两列中的数据合并为一列的一种简单方法是将两列中的所有数据复制到记事本中。记事本的搜索和替换功能是快速将两条数据格式化为一个的有效方法。
- 复制(Copy)要合并的两列中的所有单元格并将它们粘贴到记事本(Notepad)中。转到列表底部，然后按键盘上的Tab 。突出显示该选项卡空间并使用(Highlight)Ctrl-C复制它。你稍后会需要这个。然后突出显示所有文本。
- 选择编辑(Edit)菜单，然后选择替换(Replace)。
- 在查找内容(Find what)字段中，按Ctrl-V粘贴制表(Tab)符。在替换为(Replace with)字段中，按空格键。这将用一个空格替换文档中的所有选项卡。
- 现在选择Replace All，记事本(Notepad)文档中的所有文本都应该正常显示，名字和姓氏之间只用一个空格隔开。
- 突出显示(Highlight)文档中的所有文本，然后按Ctrl-C进行复制。返回您的工作表并使用Ctrl-V粘贴到您最初复制的第一列的顶部。
- 最后，只需重命名第一列并删除第二列。现在您的电子表格将两列合并为一列。
这不是在Excel中合并列的最复杂的方法，但它很有效，而且很简单。
(Merge Columns)使用 Concatenate 合并 Excel 中(Excel Using Concatenate)的列
如果您确实想要更复杂一点并在流程中节省一些步骤，您可以使用Excel中的(Excel)CONCATENATE函数。此函数的工作方式与Google Sheets 中的 Concatenate 函数(Concatenate function in Google Sheets)一样。
此功能将多个单元格中的文本合并为一个。您甚至可以使用它来合并任意数量的列，而不仅仅是两列。
- 为此，请右键单击要合并的两个列右侧的列，然后选择Insert。这将插入一个新的空白列。
- 在这个新空白列顶部的第一个空白单元格中，键入=CONCATENATE(C3,” “,D3)。将 C3(Replace C3)和 D3 替换为包含您的数据的任何单元格。中间的“”在两个单元格的文本之间添加了一个空格。当你按下回车键时，你会看到两个单元格中的数据放在一起，它们之间有一个空格。
- 最后，要将此函数复制到整列并合并两列中的数据，请按住键盘上的Shift并将鼠标悬停在第一个单元格的右下角，直到鼠标图标变为两条水平线。然后双击鼠标左键。
这是Excel中的自动填充功能。只要左侧列中有数据，它就会将公式填充到该列的底部。现在您可以看到整列都将前两列的数据合并在一起。
但是，为了删除前两列，您需要复制整个新列并将其重新粘贴为值。
完成此操作后，您可以删除两列旧列，电子表格现已完成，两列合并为一列。
如何在 Excel 中合并行
在Excel(Excel)中合并行没有快速简便的技巧或功能。您需要使用与合并列相同的CONCATENATE函数。(CONCATENATE)
然而，技术有点不同。人们将来自不同行的数据合并到一个新行中并不常见，但偶尔可能会出现这种需要。
使用连接合并行
例如，在我们一直使用的示例电子表格中，如果您想将一个月中的所有团队成员姓名合并到不同页面上的新行中怎么办？为此，您需要连接名称并使用逗号等字符将它们分开。
- 将光标放在要放置多行数据的位置。例如，您可以将光标放在新工作表中。然后键入=CONCATENATE(Sheet1!C3,”,”,Sheet1!C4)。通过选择要连接的另一个工作表中的实际单元格来替换 C3和 C4。(Replace C3)
- 当您按下Enter时，您将看到连接的数据出现在新行中，以逗号分隔。要合并更多行，只需将第一个单元格复制并粘贴到第二个新行中，然后编辑公式以包含要合并的原始工作表中的行。
您可以继续此过程，直到您合并了您想要的原始工作表中的所有行。请记住(Remember)，如果需要，您可以冻结 Excel 中的第一行，(freeze the top row in Excel)以便在处理数据时仍然可以看到标题。
使用合并和中心(Center)合并Excel 中(Excel Using Merge)的行(Rows)
当然，Merge & Center确实适用于包含相同数据的多行。例如，在这个电子表格中，二月份(February)的两个条目都来自“中部”地区。您可以将这两行合并为一行，而不是重复两次。
- 突出显示(Highlight)要合并的两行。
- 在主(Home)菜单中，从功能区中选择合并(Merge)和居中(Center)。
如您所见，这会将包含相同数据的两行合并为包含其中一个重复数据的单行。
这是清理电子表格(clean up your spreadsheets)并减少整个数据集中重复项的明智方法。
这些是在Excel(Excel)中合并单元格、列和行的一些最快技巧。你认识其他人吗？在下面的评论部分分享它们！
How To Merge Cells, Columns & Rows In Excel
When working with data in Excel, іt’s common to merge cells, columns, and rows to combine numbers or text.
There are different reasons you may need to merge information in Excel. Sometimes it’s to improve formatting. Other times it’s to bring information from multiple cells into a single cell.
Whatever the reason, you can use the following methods to combine information in Excel in any way you need to.
How to Merge Cells in Excel
One of the most common reasons to merge multiple cells is when you’re creating a title row in your Excel spreadsheet.
- You can create the title text inside any of the cells that you’d like to merge. Format the text to appear however you’d like, including font size, style, and vertical alignment or height.
- Next, you’ll want to make sure the text is aligned perfectly over the width of your data table. To do this, select all of the cells that you want to merge and center.
- With all cells you want to merge selected, on the Home menu select Merge & Center. This will combine all selected cells into one single cell, and center the text or data in that cell.
You can do the same for cells vertically as well. For example, if you want to have the name of the month centered vertically on the right side of all rows for that month, you can do this using Merge Cells.
To do this:
- Type the name of the month in the first cell.
- Next, select all of the cells that are relevant. So in this case, January would include the first and second cells.
- Now, with those cells selected, in the Home menu select the dropdown arrow next to Merge & Center. Select Merge Cells from the dropdown menu.
This will merge those cells in Excel into one and place the text at the bottom of the cell by default. The reason you don’t want to use Merge & Center in this case is because that will center the text horizontally in the column.
In this case, you’d probably prefer to center the text vertically so it’s in the center of all of the cells it applies to. To do this just select the newly merged cell, and select the vertical center alignment icon in the Home menu in the Alignment ribbon group.
This aligns the text vertically with all of the relevant cells so that everything lines up perfectly.
How to Merge Columns in Excel
In Excel, you aren’t limited to merging individual cells. You can also merge entire columns of data.
For example, in this example spreadsheet, you may want to combine the First Name and the Last Name of the sales associate into another column for their full name.
In this spreadsheet, if you select all of the data in the two columns and try to merge the cells using Merge & Center, you’ll see an error message that you’ll lose the data in all of the cells except the first cell in the range.
This isn’t useful at all.
There are a couple of quick ways you can combine all data from two columns without losing anything.
Merge Columns Using Notepad
One easy way to combine data from the two columns into one is copying all of the data from the two columns into notepad. Notepad’s search and replace feature is an effective way to quickly format the two pieces of data into one.
- Copy all of the cells from the two columns you want to merge and paste them into Notepad. Go to the bottom of the list and press Tab on your keyboard. Highlight that tab space and copy it using Ctrl-C. You’re going to need this later. Then highlight all of the text.
- Select the Edit menu, and select Replace.
- In the Find what field, press Ctrl-V to paste the Tab character. In the Replace with field, press the spacebar. This will replace all tabs in the document with a single space.
- Now select Replace All, and all of the text in the Notepad document should appear normal, with the first name and last name separated by just a single space.
- Highlight all text in the document and press Ctrl-C to copy. Go back to your sheet and paste using Ctrl-V into the top of the first column you originally copied from.
- Finally, just rename the first column and delete the second one. Now your spreadsheet has the two columns merged into one.
This isn’t the most sophisticated way to merge columns in Excel, but it works and it’s easy.
Merge Columns In Excel Using Concatenate
If you do want to get a little more sophisticated and save a few steps in the process, you could use the CONCATENATE function in Excel. This function works just like the Concatenate function in Google Sheets.
This function combines text from multiple cells into one. You could even use it to merge as many columns as you want, not just two.
- To do this, right click the column to the right of the two you want to merge and select Insert. This will insert a new, blank column.
- In the first blank cell at the top of this new blank column, type =CONCATENATE(C3,” “,D3). Replace C3 and D3 with whatever cells contain your data. The “ “ in the middle adds a space between the text from the two cells. When you press enter, you’ll see the data from the two cells put together with a space between them.
- Finally, to copy this function down the entire column and merge data from both of the columns, hold down Shift on your keyboard and hover your mouse over the lower right corner of the first cell until the mouse icon changes to two horizontal lines. Then double-left click the mouse.
This is an automated fill feature in Excel. It’ll fill the formula to the bottom of that column as far as there’s data in the column to the left. Now you can see the entire column has data from the first two columns merged together.
However, in order to delete the first two columns, you’ll need to copy the entire new column and repaste it as values only.
Once you do this, you can delete the two old columns and your spreadsheet is now finished, with the two columns merged into one.
How to Merge Rows in Excel
There is no quick and easy trick or feature to merge rows in Excel. You’ll need to use the same CONCATENATE function as you did for merging columns.
However the technique is a little different. It isn’t very common for people to combine data from different rows into a new row, but the need may arise occasionally.
Merge Rows Using Concatenate
For example, in the sample spreadsheet we’ve been using, what if you wanted to combine all of the team member names from a single month into a new row on a different page? To do this, you’d need to concatenate the names and separate them using a character like a comma.
- Place the cursor where you want to place the data from multiple rows. For example you might place the cursor in a new sheet. Then type =CONCATENATE(Sheet1!C3,”,”,Sheet1!C4). Replace C3 and C4 by selecting the actual cells in the other sheet that you wanted to concatenate.
- When you press Enter, you’ll see the concatenated data appear in the new row, separated by commas. To merge more rows, just copy and paste the first cell into the second new row, and edit the formula to include the rows from the original sheet that you want to merge.
You can continue this process until you’ve merged all of the rows from the original sheet that you want to. Remember, you can freeze the top row in Excel if you need to so that you can still see the header while you’re working on the data.
Merge Rows In Excel Using Merge & Center
Of course the Merge & Center does work for multiple rows that hold the same data. For example in this spreadsheet, both February entries are from the “Central” region. Instead of repeating this twice, you could merge these two rows into one.
- Highlight both rows that you want to merge.
- In the Home menu, select Merge & Center from the ribbon.
As you can see, this combines the two rows containing the same data into a single row containing one of those duplicates.
This is a smart way to clean up your spreadsheets and reduce duplicates throughout the data set.
These are a few of the quickest tips to merge cells, columns, and rows in Excel. Do you know any others? Share them in the comments section below!
