分享execl中自定义排序和特定排序

开场自我表述：一个简单的操作，一份真诚的分享，现在分享execl中自定义排序和特定排序的操作过程和技巧分享给大家，过程简单看图就会做，教程是自己原创的，其他分享平台估计也能看。、

系统提供的特定排序方式

第一步：我们在办公的过程中会遇到各种各样的问题，在用到EXECL表格的时候不可避免的是排序这个步骤，今天小编给大家分享两种排序方法，现在先将系统提供特定的排序方法，主要是降序 升序 按颜色排序（前提单元格有颜色）。

第二步：我们用一个简单的例子来测试下，其实例子都大同小异，没啥大区别，我们要做的就是认真，在EXECL中一不小心就会出错，认真第一。选择数据的表头就是一列的名称，然后选择排序和筛选里面的筛选，点击之后表头单元格右侧就会出现带有倒三角的小按钮。

第三步：既然都出现按钮了，那么我们肯定要点击按钮了，点击之会初夏下拉弹框，这个时候我们可以根据自己的需要选择相应的排序方式，有升序 、降序、按颜色排序等。这里我们选择降序，这个是按文字的首字母排序的。从Z到A，点击之后我们看看结果。

这是第一种系统特定排序方式，你会了吗？

接下来我们看看如何自定义排序：

采用自定义排序的方式

第一步：我们还用同样的例子，来进行操作，首选中要排序的单元格区域，然后右击-排序-自定义排序，点击之后会弹出一个弹框。这时候我们要选择次序-里面的自定义序列按钮。

第二步:这时候我们就可以按自己的意愿来安排这些数据的排列顺序，不再局限字母降序和升序的固定模式。数据之间要用英文逗号给隔开，不能是中文逗号。这个很关键，然后点击添加，这要就制作好排序了。

第三步：再设置下 主要关键字就是表头，和次要排序依据数值，选择自己制作的序列然后点击确定就可以了。结果也给大家展示出来了，可以看看。

两种方法都说完了，你学会了吧！

图/文 | 安伟星

早就承诺大家要写一篇Excel制作下拉菜单的教程，一直拖了这么久，这次用一篇文章让你完全掌握！

下拉菜单，从制作方法上，可以分为数据有效性法、控件法；从功能上，可以分为一级下拉菜单、多级联动下拉菜单、查询下拉菜单。

01、下拉菜单制作方法

下拉菜单有两者制作方法，最常用的是我们熟知的数据有效性，其实Excel中还有一个工具可以制作下拉菜单，它就是控件。

由于控件灵活性非常强，篇幅有限，本文只做简要介绍，将主要精力放在数据有效性上面。

①数据有效性法

数据有效性在2016版Excel中叫做数据验证。

如图所示，需要为部门列设置一级下拉菜单，设置下拉菜单之后，不仅能够提高录入效率，而且可以有效防止不规范地输入。

Step1：选择要添加下拉菜单的单元格C2:C7，切换到「数据」选项卡，点击「数据验证」

Step2：验证条件中，「允许」中选择「序列」

Step3：「来源」框内选择已制作好的列表区域（也可手动录入选项，选项之间用英文状态下的逗号隔开）

GIF动图演示

②控件法

控件是Excel中比较高级的一种功能，多用于VBA开发。它被集成在「开发工具」选项卡。控件法创建的下拉菜单，多数用于数值的选择，一般创建的较少，不能批量创建。

Excel中的控件

如果你的Excel中，没有开发工具这个选项卡，需要先在「自定义功能区」中将「开发工具」添加进来。

勾选如下图中的开发工具即可。

创建方法：

切换到在「开发工具」选项卡，在「控件」分区，点击「插入」，选择「组合框」控件

在工作表的任意位置绘制生成控件，选中控件点击「鼠标右键」→「设置控件格式」，在弹出的对话框中设置数据源区域，其他项保持默认即可。

控件的使用非常灵活，它和OFFSET函数、CHOOSE函数、MTATCH函数、INDEX函数等结合，能制作出非常高效的动态图表，这里不详细展开。

可以看出，不管是是用数据验证还是控件，制作一级下拉菜单都非常简单，其本质就是将下拉菜单中的数据作为数据源提前存储在菜单中，我们要做的就是设置好数据源即可，Excel自身会生成菜单。

02、多级联动下拉菜单

首先制作二级联动菜单。

二级联动菜单指的是，当我们选择一级菜单之后，对应的二级菜单会随着一级菜单的不同而选项也不同。二级菜单的创建方法有很多种，这里我们讲最常用的：通过indirect函数创建

如图所示，我们要创建省份是一级下拉菜单，对应的市名是二级下拉菜单的联动菜单。

①为省市创建“名称”

名称是一个有意义的简略表示法，可以在Excel中方便的代替单元格引用、常量、公式或表。

比如将C20:C30区域定义为名称：MySales，那么公式=SUM（MySales）可以替代=SUM（C20:C30），可见名称比单元格区域更具有实际意义。

按住Ctrl键，分别用鼠标选取包含省、市名的三列数据，要点是不要选择空单元格。（也可以通过Ctrl+G调出定位条件，设置定位条件为在常量来选取数据区域）

在菜单栏中切换到【公式】选项卡→选择【定义的名称】分区→点击【根据所选内容创建】，在弹出的菜单中，勾选【首行】选项，如图所示，这样就创建了三个省份的“名称”，“名称”的值为对应着城市名。

②创建联动菜单

创建一级菜单

为区域中的省份一列创建一级菜单，创建方法通过“引用区域”的方式，直接将第一个图中的B1:D1区域作为数据来源，这里不在赘述。

为上图中的“市”创建二级菜单

选中【市】列需要设置的单元格区域→在验证条件中选择【序列】→【来源】中输入公式=INDIRECT($C3)→点击【确定】，此时会弹出错误提示，点击【是】继续下一步即可，如图。

提示：这里出错的原因是此时C3单元格中为空，还未选择省份的数据，找不到数据源，不影响二级菜单的设置。

完成之后，就实现了二级联动菜单，如图所示。

原理解析

实现二级联动菜单的核心是：定义名称和INDIRECT函数，理解这两个核心是解题的关键。

原理①：根据“名称”的作用，当我们定义名称“江苏省”时，那么在函数引用中，“江苏省”能够代替“南京、苏州……”

原理②：INDIRECT函数为间接引用，他可将文本转化为引用。

如图是间接引用于直接引用的不同。

将原理①和原理②结合起来，以江苏为例，在来源中输入的公式=INDIRECT（$C3）的意思是，首先C3单元格中的值是“江苏省”，而INDIRECT可以将文本换成引用，而“江苏省”已经定义为名称，代表的是“南京、苏州……”，所以二级下拉菜单中出现的南京市、苏州市等。

多级下拉菜单的制作原理是完全一样的，学会了二级下拉菜单，三级菜单甚至四级菜单应该也不成问题，自己动手试一试吧！

03、查询式下拉菜单

下拉菜单的目的之一是提高输入的效率，但是，如果选项过多，那么下拉列表势必会很长，此时要想快速从下拉菜单中找到目标选项就非常困难。

我经常在想，如果能进行搜索下拉菜单该多好啊，这里教给你的方法，虽然没有搜索框，但是能模拟搜索的效果。

我把它称为查询式下拉菜单。

如图，要根据A列的集团列表，在E2单元格创建查询式下拉菜单，更方便地选择集团。该下拉菜单可以根据E2单元格内输入的第一个字来动态显示所有以输入汉字开头的集团，即实现查询作用。

对A列的集团进行升序排序。

选中E2单元格，打开「数据验证」对话框。在“允许”中选择“序列”，并在“来源”中输入公式：

=OFFSET($A$1,MATCH($E$2&"*",$A$2:$A$15,0),,COUNTIF($A$2:$A$15,$E$2&"*"),1)

在「数据验证」对话框，切换到「出错警告」窗口，取消勾选「输入无效数据时显示出错警告」，然后点击确定，完成设置。

最终的效果如下动图所示：

操作步骤同样很简单，难点是来源里面设置的公式。

①为什么要对集团数据列进行升序排序

排序之后，可以将第一个字相同的集团排在一起，这样在后面的输入首字进行查询式，这些集团都能够显示出来。

②OFFSET函数

它的语法形式是 OFFSET(reference,rows,cols,height,width)，参数1为参照系，参数2为偏移行数，参数3为偏移列数，参数4为返回几行，参数5为返回几列。

总之，这里主函数OFFSET的作用就是：当E2单元格内输入首字时，找到以输入的汉字开头的集团名称，并引用所有符合条件的集团作为下拉菜单的显示内容。

③MATCH($E$2&"*",$A$2:$A$15,0)

在集团列表中查找以E2单元格字符开头的集团名称，返回找到的对应的第一个集团在列表中的序号；

④COUNTIF($A$2:$A$15,$E$2&"*")

在列表中统计以E2中字符开头的集团的个数

这里，MATCH函数作为OFFSET的第二个参数，即向下移动的行数；COUNTIF函数作为OFFSET的第4个参数，即从集团列表中返回的行数。

举例：当E2中输入“广”时

MATCH($E$2&"*",$A$2:$A$15,0)返回以广开头的集团在$A$2:$A$15中的序号，即2（广发集团排在第二位）。

此时COUNTIF($A$2:$A$15,$E$2&"*")统计出以广开头的集团共有三个，所以返回值为3。

主函数就变为OFFSET($A$1,2,,3,1)，即返回「以A1为参照，向下移动移动两行（A3），行数总计为3行（A3:A5）的一个区域」，这个区域正是以广开头的三家集团：广发集团、广汇集团、广汽集团。

⑤为什么不能勾选出错警告

数据验证，要求输入的内容和设置的源中的内容必须一致，否则将提示警告，导致无法正常输入。我们因为是首字匹配，因此要取消警告。

最后，再次强调，函数是重点，理解了函数在本里中充当的含义，才能灵活的设置查询式下拉菜单。

·The End·

作者：安伟星，微软Office认证大师，领英中国专栏作者，《竞争力：玩转职场Excel，从此不加班》图书作者

在Excel中设置下拉菜单很简单，直接用数据有效性-序列就可以实现。

今天我们介绍的下拉菜单：

可以显示多列内容选取后只输入其中一列的内容。

制作步骤：

一、 生成多列下拉列表

1、添加辅助列，用&把两列连接起来

2、数据有效性-序列，引用C列合并后的数据生成下拉菜单

二、有选择性的显示列内容

1、在工作表标签上右键 - 查看代码 - 点击新打开窗口中右上角的sheet1（当前生成下拉菜单的工作表名称），然后把下面的代码粘贴到右侧的窗口中（不需要此功能时删除代码保存即可）

Private Sub Worksheet_Change(ByVal Target As Range)On Error Resume NextIf Target.Row > 1 And Target.Column = 5 And Target <> "" Then'1 表示下拉列表从1行下面开始, 5 是下拉列表所在的列数Application.EnableEvents = False Target = Split(Target, " ")(0)'显示第1列用0,第2列用1，以此类推 Application.EnableEvents = TrueEnd IfEnd Sub

2、当前文件另存为“Excel 启用宏的工作簿" (2003版此步忽略)

完工！下面用动画展示我们的成果吧！

选取后显示第一列内容

通过修改代码（把0改为1），选取后显示第二列内容

Excel说：今天VBA又露脸了。在Excel中VBA就是这么牛，一般函数和功能实现不了的，它就可以帮你实现。

在工作中，我们人事部工作人员常常要将新入职员工输入到各自归属的部门表格中，为了保持名称的一致性，利用“数据有效性”功能建立一个分类下拉列表填充项是个非常实用的办法。

如下图所示，我们希望在每次录入员工部门归属时不用再复制黏贴上面的部门，而是用下拉菜单方式选择某一个部门。这样既快捷又准确。

下面讲一下操作步骤：

第一步：选中B列单元格，单击功能选项卡【数据】下面的【数据有效性】下拉菜单中的【数据有效性】，出现【数据有效性】对话框。

第二步：在【有效条件】下面选择序列，然后下面会出现【来源】选项，区域选择B列一整列，单击【确定】

这样操作后鼠标放在B列的任何单元格右下角都会出现个小箭头，然后点击小箭头就可以选择任意部门啦！这样是不是简单又准确呢！

大家先感受下这个好用的可搜索下拉列表吧：

看到了吗？当下拉选项较多时，普通的下拉列表，只能睁大了眼睛仔细找了，说不定还选串行了得重选，但是可搜索的下拉列表就不一样了，可以按你输入的条件过滤下拉选项，大大缩小了肉眼查找的范围，是不是好用多了？

了解了它的好处后，咱们就再看看怎么实现吧，稍微有点复杂，但是别担心，一步一步慢慢来，就能搞定它！

第一步：按搜索值对下拉选项进行编号

首先，要为产品编号列增加一个辅助的动态ID，我们将F列作为动态ID列，设置F2单元格公式为：=IF(ISNUMBER(SEARCH($B$2,$G$2:$G$22)),MAX($F$1:F1)+1,0)，右下角下拉复制到F22单元格；

整个公式的含义：以B2单元格的输入值作为查询条件，在“产品编号”范围内查找，若包含查找值，则对其进行编号，编号依次从1开始递增，而不包含查找值的编号为0；

公式有点长？那我们分解一下，每步的动图可以帮助你理解：

1、首先是Search函数，在$G$2:$G$22的固定区域内查找$B$2输入的值，若找到，则Search函数返回1，否则返回错误值；

2、ISNUMBER函数判断Search的返回值是否是数值，若是则返回true，否则返回false；

3、IF函数接着判断若为true，则返回MAX($F$1:F1)+1，否则返回0；

4、MAX函数查找固定从F1单元格开始到F1单元格终止的区域中的最大值；注意：终止单元格使用的是相对引用，使得符合条件的编号可以实现从1开始递增的效果；

第二步：按搜索值列出自动建议列表

将J列作为自动建议列表列，在J2单元格输入公式：=IFERROR(VLOOKUP(ROWS($J$2:J2),$F$2:$G$22,2,0),"")，右下角下拉复制到J22单元格；

整个公式的含义：根据B2单元格的输入值，将符合过滤条件的产品编号，逐一罗列在J列。

公式分解：

1、 ROWS函数获取从1到21（即总的产品编号个数）的自然数；

2、 VLOOKUP函数根据1~21的自然数，到$F$2:$G$22固定区域中查找对应编号的产品编号值，即符合B2输入值查询条件的产品编号，依次填入J列的各单元格中；

3、 IFERROR函数将J列单元格中的错误值转换为空；

第三步：构造可搜索下拉列表的选值区域

在I列构造最终选择值范围，在I2单元格输入公式：=OFFSET($J$2,,,COUNTIF($J$2:$J$22,"?*"))，公式含义：获取自动建议列表中有数据的值；

公式分解：

1、 COUNTIF函数查找J列自动建议列表列中有数据的单元格总个数；

2、 OFFSET函数返回J列中所有有数据的单元格区域；

第四步：创建选值区域的名称

复制上一步构造的选值区域的公式，在名称管理器中，粘贴到新建名称的“引用位置”中，并输入名称：

第五步：创建可搜索下拉列表

选中B2单元格，维护数据验证，设置验证条件的“允许”为“序列”，并在“来源”中按F3，调出“粘贴名称”界面，选中上一步创建的名称：

OK，大功告成！好用的可搜索下拉列表归你了！

当Excel表格下拉菜单中的选项非常多时，你就需要一个搜索式下拉菜单。

就像百度搜索框一样，输入一部分内容，就会自动联想出相关的选项供你选择，无关的会自动被过滤掉。例如输入一个字“蔡”，就会把所有姓“蔡”的姓名都列出来。

而如果你使用普通的下拉菜单，你要拖到什么时候才会找到自己想要的数据？还不如不用下拉菜单呢。

所以，搜索式下拉菜单是不是挺实用的？

制作搜索式下拉菜单的步骤

先给原始数据按照姓名排序，接着就和普通的下拉菜单一样创建序列，在“来源”中输入公式“=OFFSET($A$1,MATCH(E2&"*",$A$2:$A$281,0),0,COUNTIF($A$2:$A$281,E2&"*"),1)”。

公式解释

整个公式其实就是一个OFFSET函数，OFFSET函数的第二个参数是个Match函数，用于获取以E2单元格内容开头的第一个匹配值的位置，例如你在E2中输入“蔡”，那么就会得到3。第四个参数是COUNTIF函数，用于统计以E2单元格内容开头的单元格数量。这样整个公式就会把包含E2单元格内容的所有选项找出来了。

如果你想要搜索出包含E2单元格内容的数据，可以将公式中的“E2&*”替换成“*E2&*”。

错误1

按照上面的步骤操作，很多人会遇到的第一个错误就是输入一个字之后，就遇到了Excel的警告。

这是因为，你没有将“数据验证”/“有效性”中的“出错警告”去掉。

错误2

输入第一个字之后，下拉菜单中的选项虽然少了很多，可是和我们输入的内容完全没有关系啊！

这是因为，你忘记了给所有原始的数据按照姓名排序。

错误3

下拉菜单搜索功能没有问题，可是没有得到“座位号”和“销量”。

这其实不是下拉菜单的错误，但因为“座位号”和“销量”是用Vlookup函数获取的（这种情况下，很多人会用Vlookup）。Vlookup函数要求数据升序排列，而表格中的姓名是降序排列的，所以得到了错误的值和空白值。

解决了所有的错误，你就可以得到完美的下拉菜单啦。

PS：这篇文章的步骤针对Excel，WPS中的下拉列表功能默认自动搜索功能，不需要这么麻烦。

相关阅读：《WPS Excel 获取动态数据函数offset的基本用法》、《WPS Excel：如何比较两列数据（match函数法）》

谢谢阅读，每天学一点，省下时间充实自己。欢迎点赞、评论、关注和点击头像。

你是不是在工作中常常需要用到excel下拉菜单？但是没有一个好的方法或者不会制作excel的下拉菜单？

今天咱们就说一个怎么快速制作excel下拉菜单的案例，让你在工作中快速并且准确的录入数据。简单粗暴并且非常有效！好了开始今天的excel下拉菜单制作：我们先看下图案例，当你输入一个关键词的时候下面就会自动弹出相关词的名称可提供给你选择，这样咱们是不是提高了工作效率？提高了输入准确度呢？

首先，如果咱们的数据源如果是放在G列的话，那么咱们就要先对G列的需要用到的数据进行升序的一个排序。

然后再选择A列的区域，依次点击“数据”→“数据验证”，允许类型选择的序列，在来源的编辑框中输入

=OFFSET($G$1,MATCH(A2&"*",$G:$G,0)-1,,COUNTIF($G:$G,A2&"*"))

（公式解析：其实公式中的G1，指的是实际数据所在列是第一个单元格，这里咱们简单了解一下就行了，公式中的$G:$G,就是咱们实际数据所在的列了）

最后一步就是切换到“数据验证”的“出错警告”选项了，把“输入无效数据时显示出错警告”前面的勾取消掉，点击确定就可以看一下咱们亲手制作的excel下拉菜单了！（PS:为什么要取消掉“输入无效数据时显示出错警告”如果咱们不取消掉的话在输入数据源中没有的数据时就会出现烦人的警告窗口）

不知道今天简单粗暴的教程是否对您有效呢？如果您还有什么更好的方法欢迎评论区交流哦!如果您学会了就点个赞吧！谢谢周末愉快！

为了提高Excel的数据录入效率，使用下拉菜单是一个不错的选择。但Excel下拉菜单怎么做？小盾这里整理了三种方法教给大家。

一、快捷键（【Alt+↓】）

步骤：选中目标单元格-按【Alt+↓】即可。

这一快捷键功能虽然便利，但必须是同列已输入内容的重复录入才适用。

二、数据有效性

1、单项下拉菜单

步骤：点击【数据】-【数据有效性】-【序列】-【来源】-选中所需内容即可。

2、多项下拉菜单

步骤：输入辅助列（Ctrl+E快速输入）-点击【数据】-【数据有效性】-【序列】-【来源】-选中辅助列即可。

下拉菜单制作就这么简单，工作效率快速提升，再也不用担心要加班啦！

选中要设置下拉菜单的单元格或单元格区域----数据---有效性---"允许"中选择"序列"---"来源"中写入下拉项的条目如:   男,女  条目之间要用英文半角逗号相隔

如果是多个数据条目的录入,且经常想进行修改那按下面的图进行制作

（本文内容由百度知道网友1975qjm贡献）

Excel表格中下拉菜单是个非常实用的功能，它可以让你快速选择你需要的选项，避免输入错误。以下表为例，E、F、G三列有部门和人员的数据，现在希望在A列制作一个下拉菜单，选项为部门名称；在B列制作一个二级下拉菜单，当A列选择好部门之后，B列下拉菜单中会显示A列部门对应的人员名单。怎样制作一级下拉菜单和多级下拉菜单呢？

一、制作一级下拉菜单。

1. 选中A2单元格，点击“数据”--》“数据验证”--》“数据验证(V)”。

2. 在弹出的数据验证窗口中，选“序列”为验证条件，选部门所在单元格为“来源”，也可以在“来源”框中直接输入部门名称。注意如果直接输入来源，来源选项之间需用英文半角逗号分隔。最后点击“确定”，部门下拉菜单就制作好啦，如图1-4。

二、制作多级下拉菜单。

1. 选中E、F、G三列所有数据，同时按Ctrl+G调出定位窗口，然后设置“定位条件”为“常量”。

2. 点击“公式”--》“根据所选内容创建”--》“首行”--》“确定”。

3. 这样，我们打开“公式”下的“名称管理器”，就会看到创建了几个名称。Excel中名称的命名有三个原则：1) 开头为字母或下划线；2) 不包含空格或不允许字符；3) 不与工作簿中的现有名称冲突。因此原始数据中部门名字命名也必须要满足这三个原则，否则上述步骤2中创建名称管理器就会出错。

4.选中B2单元格，点击“数据”--》“数据验证”--》“数据验证(V)”，再次制作一个一级下拉菜单，选“序列”为验证条件，“来源”框中输入“=INDIRECT($A2)”，最后点击“确定”，二级下拉菜单就制作好啦。

5. 选中A2-B2，向下填充，复制单元格内容。这样当A列选了部门之后，B列下拉菜单中就只会出现该部门下的人员清单啦。

如果还想制作三级下拉菜单，例如制作地址管理表（省\市\县），请参考二级下拉菜单制作方法。