excel删除重复的行_如何在Excel中删除重复的行

excel删除重复的行

When you are working with spreadsheets in Microsoft Excel and accidentally copy rows, or if you are making a composite spreadsheet of several others, you will encounter duplicate rows which you need to delete. This can be a very mindless, repetitive, time consuming task, but there are several tricks that make it simpler.

当您在Microsoft Excel中使用电子表格并意外复制行时，或者在制作包含其他多个电子表格的复合电子表格时，会遇到需要删除的重复行。这可能是一项很漫不经心，重复，耗时的任务，但是有一些技巧使之变得更简单。

入门 (Getting Started)

Today we will talk about a few handy methods for identifying and deleting duplicate rows in Excel. If you don’t have any files with duplicate rows now, feel free to download our handy resource with several duplicate rows created for this tutorial. Once you have downloaded and opened the resource, or opened your own document, you are ready to proceed.

今天，我们将讨论一些方便的方法来识别和删除Excel中的重复行。如果您现在没有文件包含重复的行，请随时下载我们的便捷资源，其中包含为本教程创建的多个重复的行。下载并打开资源或打开自己的文档后，就可以继续进行。

选项1 –在Excel中删除重复项 (Option 1 – Remove Duplicates in Excel)

If you are using Microsoft Office Suite you will have a bit of an advantage because there is a built in feature for finding and deleting duplicates.

如果您使用的是Microsoft Office Suite，则将有一个优势，因为它具有用于查找和删除重复项的内置功能。

Begin by selecting the cells you want to target for your search. In this case, we will select the entire table by pressing “Control” and “A” at the same time (Ctrl + A).

首先选择要定位的搜索单元。在这种情况下，我们将同时按下“ Control”和“ A”(Ctrl + A)来选择整个表格。

Once you have successfully selected the table, you will need to click on the “Data” tab on the top of the screen and then select the “Remove Duplicates” function as shown below.

成功选择表格后，将需要单击屏幕顶部的“数据”选项卡，然后选择“删除重复项”功能，如下所示。

Once you have clicked on it, a small dialog box will appear. You will notice that the first row has automatically been deselected. The reason for this is that the “My data has headers” box is ticked.

单击后，将出现一个小对话框。您会注意到第一行已自动取消选择。原因是在“我的数据有标题”框中打了勾。

In this case, we do not have any headers since the table starts at “Row 1.” We will deselect the “My data has headers” box. Once you have done that, you will notice that the whole table has been highlighted again and the “Columns” section changed from “duplicates” to “Column A, B, and C.”

在这种情况下，由于表从“行1”开始，所以我们没有任何标题。我们将取消选择“我的数据有标题”框。完成此操作后，您会注意到整个表再次被突出显示，并且“列”部分从“重复项”更改为“列A，B和C”。

Now that the entire table is selected, you just press the “OK” button to delete all duplicates. In this case, all the rows with duplicate information except for one have been deleted and the details of the deletion are displayed in the popup dialog box.

现在已经选择了整个表格，您只需按“确定”按钮即可删除所有重复项。在这种情况下，所有具有重复信息的行(一个除外)都将被删除，并且删除的详细信息将显示在弹出对话框中。

选项2 – Excel中的高级过滤 (Option 2 – Advanced Filtering in Excel)

The second tool you can use in Excel to Identify and delete duplicates is the “Advanced Filter.” This method also applies to Excel 2003. Let us start again by opening up the Excel spreadsheet. In order to sort your spreadsheet, you will need to first select all using “Control” and “A” as shown earlier.

在Excel中可以用来识别和删除重复项的第二个工具是“高级筛选器”。此方法也适用于Excel2003。让我们从打开Excel电子表格开始。为了对电子表格进行排序，您需要首先使用“控件”和“ A”选择所有内容，如前所示。

After selecting your table, simply click on the “Data” tab and in the “Sort & Filter” section, click on the “Advanced” button as shown below. If you are using excel 2003, click on the “Data” drop down menu then “Filters” then “Advanced Filters…”

选择表格后，只需单击“数据”选项卡，然后在“排序和过滤器”部分中，单击“高级”按钮，如下所示。如果您使用的是excel 2003，请单击“数据”下拉菜单，然后单击“过滤器”，然后单击“高级过滤器...”。

Now you will need to select the “Unique records only” check box.

现在，您需要选中“仅唯一记录”复选框。

Once you click on “OK,” your document should have all duplicates except one removed. In this case, two were left because the first duplicates were found in row 1. This method automatically assumes that there are headers in your table. If you want the first row to be deleted, you will have to delete it manually in this case. If you actually had headers rather than duplicates in the first row, only one copy of the existing duplicates would have been left.

一旦您单击“确定”，您的文档应具有所有重复项，但其中一个已删除。在这种情况下，剩下两个，因为在第1行中找到第一个重复项。此方法自动假定您的表中有标题。如果要删除第一行，在这种情况下，您将必须手动删除它。如果您实际上在第一行中具有标题而不是重复项，那么将仅保留现有重复项的一个副本。

选项3 –更换 (Option 3 – Replace)

This method is great for smaller spreadsheets if you want to identify entire rows that are duplicated. In this case, we will be using the simple “replace” function that is built into all Microsoft Office products. You will need to begin by opening the spreadsheet you want to work on.

如果要标识重复的整个行，此方法非常适合较小的电子表格。在这种情况下，我们将使用所有Microsoft Office产品中内置的简单“替换”功能。您首先需要打开要处理的电子表格。

Once it is open, you need to select a cell with the content you want to find and replace and copy it. Click on the cell and press “Control” and “C” (Ctrl + C).

打开后，您需要选择一个包含要查找内容的单元格，然后替换并复制它。单击单元格，然后按“控制”和“ C”(Ctrl + C)。

Once you have copied the word you want to search for, you will need to press “Control” and “H” to bring up the replace function. Once it is up, you can paste the word you copied into the “Find what:” section by pressing “Control” and “V” (Ctrl + V).

复制要搜索的单词后，您需要按“ Control”和“ H”以调出替换功能。一旦启动，您可以通过按“ Control”和“ V”(Ctrl + V)将复制的单词粘贴到“查找内容：”部分。

Now that you have identified what you are looking for, press the “Options>>” button. Select the “Match entire cell contents” checkbox. The reason for this is that sometimes your word may be present in other cells with other words. If you do not select this option, you could inadvertently end up deleting cells that you need to keep. Ensure that all the other settings match those shown in the image below.

现在，您已经确定了要查找的内容，请按“选项>>”按钮。选择“匹配整个单元格内容”复选框。这样做的原因是有时您的单词可能与其他单词一起出现在其他单元格中。如果不选择此选项，则可能会无意间导致删除需要保留的单元格。确保所有其他设置与下图中显示的设置匹配。

Now you will need to enter a value in the “Replace with:” box. For this example, we will use the number “1.” Once you have entered the value, press “Replace all.”

现在，您需要在“替换为：”框中输入一个值。在此示例中，我们将使用数字“ 1”。输入值后，按“全部替换”。

You will notice that all the values that matched “dulpicate” have been changed to 1. The reason we used the number 1 is that it is small and stands out. Now you can easily identify which rows had duplicate content.

您会注意到，所有与“ dulpicate”匹配的值都已更改为1。我们使用数字1的原因是它很小并且很突出。现在，您可以轻松确定哪些行包含重复的内容。

In order to retain one copy of the duplicates, simply paste the original text back into the first row that has been replaced by 1’s.

为了保留重复项的一个副本，只需将原始文本粘贴回已替换为1的第一行中。

Now that you have identified all the rows with duplicate content, go through the document and hold the “Control” button down while clicking on the number of each duplicate row as shown below.

现在，您已经识别出所有具有重复内容的行，浏览文档并按住“ Control”按钮，同时单击每个重复行的编号，如下所示。

Once you have selected all the rows that need to be deleted, right click on one of the grayed out numbers, and select the “Delete” option. The reason you need to do this instead of pressing the “delete” button on your computer is that it will delete the rows rather than just the content.

选择完所有需要删除的行后，右键单击灰色的数字之一，然后选择“删除”选项。您需要执行此操作而不是按下计算机上的“删除”按钮的原因是，它会删除行，而不仅仅是内容。

Once you are done you will notice that all your remaining rows are unique values.

完成后，您会注意到所有剩余的行都是唯一值。

翻译自: https://www.howtogeek.com/198052/how-to-remove-duplicate-rows-in-excel/