如果您使用名称数据集,对其进行排序是您必须经常执行的常见任务之一。
根据全名按字母顺序对数据进行排序非常容易,其中Excel 使用名称的第一个字符进行排序。
但是,如果您想在Excel 中按姓氏对数据进行排序怎么办?
虽然它不是那么简单,但仍然可以完成(很大程度上还取决于名称数据的结构方式)。
无论您使用哪种方法,您都必须以某种方式从全名中提取姓氏并将其放在单独的列中。然后,您可以使用此列按姓氏字母顺序对数据进行排序。
在本Excel 教程中,我将向您展示如何根据姓氏对具有名称的列进行排序。
所以让我们开始吧!
使用查找和替换按姓氏提取和排序
按姓氏排序的第一步是在单独的列中获取姓氏。
您可以通过用空白替换姓氏之前的所有内容来做到这一点,这样您就只剩下姓氏了。
假设您有一个如下所示的数据集,并且您希望使用姓氏按字母顺序对该数据进行排序。
以下是按姓氏排序的步骤:
- 选择包含标题的数据集(在本例中,它将是A1:A10)
- 将其复制到相邻列中(如果相邻列不为空,则插入新列,然后复制这些名称)
- 重命名复制的列标题。在此示例中,我将命名为“姓氏”
- 选择所有复制的名称(不要选择标题)
- 按住Control 键,然后按H 键。这将打开查找和替换对话框。
- 在查找内容字段中,输入*(星号后跟空格字符)
- 将替换为字段留空
- 单击全部替换。这将立即替换所有名字,您将只剩下姓氏。
上述步骤将保留姓氏并删除它之前的所有内容。即使您有中间名或前缀(例如Mr. 或Ms),这也很有效。
在相邻列中获得姓氏后,您可以轻松地根据姓氏按字母顺序对数据集(包括全名)进行排序。
以下是按姓氏排序的步骤:
- 选择带有标题的整个数据集(包括全名和提取的姓氏)。您还可以包括要与名称一起排序的其他列
- 单击数据选项卡
- 点击排序
- 在“排序”对话框中,确保选中“我的数据有标题”。
- 在“排序依据”选项中,选择只有姓氏的列的名称
- 在“排序方式”中,选择“单元格值”
- 在“订单”选项中,选择“A 到Z”
- 点击确定
上述步骤将根据姓氏对整个选定的数据集进行排序。
完成后,您可以删除具有姓氏的列。
专业提示:在任何时候,如果您认为您可能需要返回原始数据,您需要有一种方法来取消对该数据集的排序。为此,在相邻列(左或右)中,在排序之前有序列号。现在,如果您需要返回原始数据,您可以通过基于数字的排序来获得它。
使用公式按姓氏提取和字母排序
虽然上面显示的方法(使用查找和替换)是我更喜欢获取所有姓氏并根据它进行排序的方法,但它的一个限制是生成的数据是静态的。
这意味着如果我在列表中添加更多名字,我将不得不再次执行相同的过程来获取姓氏。
如果这是您不想要的,您可以使用公式方法按姓氏对数据进行排序。
假设您有如下所示的数据集。
下面是从全名中提取姓氏的公式:
= RIGHT(A2,LEN(A2)-FIND(“”,A2))
上面的公式依赖于具有全名的模式(在此示例中仅包含名字和姓氏)。模式是名字和姓氏之间会有一个空格字符。
FIND 函数用于获取空格字符的位置。然后从名字的总长度中减去这个值,得到姓氏中的字符总数。
然后在RIGHT 函数中使用该值来获取姓氏。
一旦你有了姓氏列,你就可以对这些数据进行排序(这在第一种方法中有详细介绍)。
当您只有名字和姓氏时,上述公式将起作用。
但是,如果您也有中间名怎么办。或者名字前可能有称呼(如先生或女士)
在这种情况下,您需要使用以下公式:
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
上面的公式找到最后一个空格字符的位置,然后用它来提取姓氏。
我建议你在所有情况下都使用第二个公式,它更简单,可以处理所有情况(只要姓在名字的末尾)。
注意:这两个公式依赖于每个名称元素之间只有一个空格字符的条件。如果有双空格或前导/尾随空格,此公式将给出不正确的结果。在这种情况下,最好使用TRIM 函数先去掉任何前导、尾随和双空格,然后使用上述公式。
虽然这似乎是一种复杂的方法,但使用公式的好处是它可以使结果动态化。如果您在列表中添加更多名称,您所要做的就是复制公式,它会为您提供姓氏。
使用文本到列
Text to Columns 又是一种在Excel 中拆分单元格的简单方法。
您可以指定分隔符(例如逗号或空格)并使用它来分割单元格的内容。在单独的列中拆分元素后,您可以使用具有姓氏的列来按字母顺序排列数据。
假设您有一个如下所示的数据集:
以下是使用Text to Column 按姓氏排序的步骤:
- 选择具有名称的列(不包括标题)
- 单击数据选项卡
- 在“数据工具”组中,单击“文本到列”选项。这将打开Text to Columns 向导
- 在“将文本转换为列向导”的第1 步中,选择“分隔”并单击“下一步”
- 在第2 步中,选择“空格”作为分隔符(如果选中则取消选中其他任何内容),然后单击下一步按钮。
- 在第3 步中,选择数据预览中的名字列,然后选择“不导入列(跳过)”选项。这可以确保名字不是结果的一部分,您只会得到姓氏。
- 同样在步骤3 中,将目标单元格更改为与原始数据相邻的单元格。这将确保您单独获得姓氏并且原始姓名数据是完整的。
- 点击完成
获得结果后,您可以按姓氏排序。
当您有逗号作为分隔符时,您还可以使用文本到列来分隔名字和姓氏。
使用闪光填充
另一种快速获取姓氏的方法是使用Flash Fill 功能。
Flash Fill 是在Excel 2013 中引入的,它通过识别模式来帮助操作数据。为此,您需要多次显示Flash Fill 您期望的结果。
一旦它识别出模式,它将迅速为您完成其余的工作。
假设您有以下名称数据集。
以下是使用Flash Fill 获取姓氏并使用它进行排序的步骤:
- 在单元格B2 中,输入文本“Maury”。这是您在单元格中期望的结果。
- 转到下一个单元格并输入相邻单元格中姓名的姓氏(本例中为Elliot)。
- 选择两个单元格
- 将光标悬停在所选内容的右下部分。您会注意到光标变为加号图标。
- 双击它(或单击并向下拖动)。这将在单元格中为您提供一些结果(不太可能是您想要的结果)
- 单击“自动填充选项”图标。
- 点击快速填充
这将为您提供可能是所有单元格中的姓氏的结果。
我说可能,因为Flash Fill 在某些情况下可能不起作用。由于它取决于识别模式,因此它可能无法始终做到这一点。或者有时,它破译的模式可能不是正确的模式。
在这种情况下,您应该在一个或两个以上单元格中输入预期结果,然后执行步骤4-7。
在列中包含所有姓氏后,您可以根据这些姓氏对数据进行排序。
因此,您可以使用以下四种不同的方式按姓氏对数据进行排序。最好的方法是使用查找和替换技术,但如果你想让你的结果动态化,公式方法是要走的路。
希望您发现本教程很有用。