Excel小技巧.docx
《Excel小技巧.docx》由会员分享，可在线阅读，更多相关《Excel小技巧.docx（32页珍藏版）》请在冰豆网上搜索。
Excel小技巧
随工作过程中搜集的一些excel技巧。
（主要是公式类）置于此与大家共享。
希望可以有所帮助，便于查找。
日常工作中关注各种小技巧，
让繁琐的事情简单化！
目录
【Excel技巧】如何实现多级联动下拉菜单效果3
EXCEL2007跨表单设置二级数据有效性9
【Excel技巧】添加多行多列作为序列的方法16
步骤/方法16
excel中怎样制作下拉菜单22
步骤/方法22
注意事项23
excel如何根据身份证号计算男女性别和年龄24
操作步骤：
24
excel怎么从身份证号中提取出生日期27
操作步骤：
27
【Excel技巧】如何实现多级联动下拉菜单效果
1.1
首先来说一下我们要实现的效果，就是我们选择1级菜单后，2级菜单里就有对应1级菜单的内容，我们选择2级菜单的项的时候，3级菜单会产生对应2级菜单项的内容，以此类推，来说一下我实现这种功能的原理，就是为不同的列的内容设置了名称，而这个名称恰好就是上一级菜单里的选择项。
这样的情况下，在数据有效性中的函数读取的其实是引用这个名词所对应的区域内的内容，也就把我们下一级的菜单内容读取出来了。
2.2
接下来，我们来准备数据，根据第一步所描述的原理，我将列名进行了颜色处理，以方便大家去理解。
3.3
从1级项到3级项，高级别项是下一级的列名，这就是我们的数据源的格式，如果有的网友问，不加这些列名可以吗？
回答是：
可以的，不加列名的话，你需要知道每列对应的上一级的内容，加上只是方便查看。
4.4
接下来的一步是Office2003同2003以上版本不同的地方，创建名称，2003需要逐一创建，而像2013可以全选以后，系统会根据选择区域自行创建，不过逐一创建也有好处，就是我们不会出现部分值出现2次的现象。
接下来，我们就逐一进行创建，首先创建“XX”的2级项内容。
选中数据区域。
5.5
点击Ctrl+F3，或者从菜单栏中选择“插入”--“名称”--“定义”。
6.6
需要注意的是，系统会默认所选区域的首单元格内容为当前工作薄名称，我们需要改为上一级的名称。
这里，它的上一级是“XX”。
7.点击“添加”，这样一个工作薄的名称就添加完成了，剩下的同理。
8.全部添加完成后，我们就完成了对所需要的数据区域的命名工作。
9.接下来，我们准备表格，我们需要将数据在表格中进行多级关联以实现我们想要的效果。
10.我们选择机构名称，然后在菜单栏中选择“数据”--“有效性”。
11.有效性条件选择“序列”，来源直接选择机构名称下的内容项。
12.确定之后，我们会发现，机构名称下就会有内容项了，当然了，这一步可能很多人都已经知道了，我就不过多解释了。
13.接下来，我们来关联2级的内容，同样在菜单栏中选择“数据”--“有效性”，有效性条件选择“序列”，但来源中，我们需要用到INDIRECT（目标单元格），然后点击确定。
14.这样，我们就会发现，2级菜单的关联已经有了，而且不会出现重复的1级菜单项。
15.接下来我们关联3级菜单，同关联2级菜单一致，在来源中，我们需要用到INDIRECT（$F14）。
16.这样，我们3级菜单也就关联完成了，包括4级，5级甚至更多，都可以这样来实现。
END
EXCEL2007跨表单设置二级数据有效性
问题
一个单元格的数据有效性的条件可以被设置为整数、小数等等,并设定其相应的取值范围。
当条件被设置为“序列”的时候,可以在数据有效性对话框的“来源”编辑框里输入一个以半角逗号分隔的字符串（例如“北京,天津,上海,重庆”）,或是指定一个引用区域（例如“=$A$1:
$A$9”）,在单击这个单元格的时候,就会显示一个下拉菜单,每行显示一个条目,供使用者选择。
可是这种方法只能直接引用当前表单的区域,而不能设置到其他表单,例如表单Sheet1的有效值序列不能被设置为“=Sheet2!
$A$1:
$A$9”。
有文章提到可以用名空间的办法来实现跨表单的引用,但是如果数据超出了引用区域的范围,例如“$A$1:
$A$9”有9项,如果现在新增了5项,就需要修改引用区域,也就是逐项改变数据有效性取值,而不能自动更新。
另外,如果是数据减少的情况,例如原本设置为“$A$1:
$A$9”共有9项,现在只剩下3项,那么单击下拉箭头之后会看到菜单中出现了大片空白。
有文章提到可以用OFFSET加COUNTA函数来解决这个问题,但如果复制这样的单元格,那么函数参数中的行或者列就会自动增加,使设置失效。
“Excel表”不是“Excel表”
为了跨表单设置数据有效性,我们需要先在Excel中插入“Excel表”。
没错,就是在Excel中插入“Excel表”。
不是“表单”,不是“工作表”,不是“数据表”,不是“数据透视表”,不是新建“空工作簿”,不是插入行或者列,是“Excel表”。
“Excel表”不是“Excel表”吗?
抱歉不是,通常听到的“Excel表”的“表”的含义,是指Excel文件,或是Excel概念框架里的“单元格区域”,或者干脆就是指的Excel软件本身。
在Excel的概念框架里,“Excel表”是对一块“单元格区域”的引用,不是“用Excel打开的表”,而是Excel在内部定义和使用的一种单元格区域的组织形式。
在没有被定义为“Excel表”之前,“单元格区域”仍然只是“单元格区域”,或者简称“区域（range）”,不论它看起来多么像一张日常生活中的表,不论其中记录了多少数字、公式。
参见Excel帮助文档“Excel表概述”。
在视图中,“Excel表”的右下方有一个蓝色的小三角,如图。
这个小三角又叫做该表的尺寸控点,表示这里有一份Excel表,而不是一块普通的单元格区域。
在图中,每一列都是一份“Excel表”,可以看到每一份“Excel表”的右下方都有代表尺寸控点的蓝色小三角。
如果用户在紧邻尺寸控点的下方单元格里输入数据后回车,控点可以自动跨越到新的数据行,并包含刚刚录入的数据,这一功能被称为“表自动扩展”。
如果需要开启或关闭“表自动扩展”这一功能,可使用“Excel选项/校对/自动更正选项/键入时自动套用格式/在表中包含新行和列”。
在Excel的概念框架里,“Excel表”、“表”、“表格”、“Excel表格”是同义词,是对一个单元格区域的引用,是这个单元格区域的别名,有自己的“名称”。
这里的“名称”与日常生活中使用的“名称”一词也有不同的含义。
日常生活中,名称是指“称谓”。
在Excel概念框架里,“名称”是一块单元格区域的“代词”,表示在这份文档里,可以用这个“名称”来代替这块区域。
在Excel文档里,当您使用一个“名称”,您实际使用的是一块单元格区域。
在Excel文档里,当您看到一个“名称”,您实际看到的是一块单元格区域。
如果您了解一点程序设计,那么“名称”代表实例,而不是类。
在“名称管理器”中可以查看到“名称”的存在,下一小节可以看到一张“名称管理器”的示意图。
创建Excel表
创建“Excel表”的方法是通过“插入”标签的“表/表”按钮。
创建“Excel表”之后,可以在“名称管理器”中看到创建的结果,如图。
“名称管理器”按钮位于“公式”标签页。
可以看到刚刚选定的单元格区域现在被“名称管理器”分解到“名称”、“数值”、“引用位置”三个属性之中分别描述。
就像前面所提到的那样,表的“名称”可以代替这一序列,可以在文档中任何一个可以使用公式的地方被引用。
在名称被引用时,它将返回完整的数值的序列。
创建的“Excel表”将被自动命名为“表X”,您可以为它重新命名,将它改为任何您需要的名字。
打开名称管理器,点击选择您所希望修改的表,再点击上方的“编辑”按钮。
有时,你可能会删除某个已经存在的“名称”,以便腾出位置,让它不占用您希望设定的名字。
然后,你可能会发现,即便删除了那个已经存在的名字,Excel却仍然提示“该名称已存在”。
这可能是因为刚刚删除的“名称”还保留在对象模型（ObjectModel）实例以内。
可以尝试保存并关闭全部Excel文档之后重新打开文件,或者稍加等待。
在“名称管理器”图中,除了“计量属性描述”以外,其他的“名称”全都是“Excel表”,可以看到“Excel表”和单纯的“名称”有不一样的图标。
“名称”是一个数值序列,它可以是上文所描述的“Excel表”,也可以仅仅是一个“名称”。
本质上都是对一块“单元格区域”的引用,不同在于“Excel表”具有尺寸控点,可以执行“表自动扩展”。
重点是,我们可以利用“引用‘名称’将返回‘名称’所指代的数值序列”这一特性来设置只有一级的跨表单的数据有效性。
第一步,选择一个有数据的单元格区域,创建一份名为“抽象属性描述”的Excel表（参考本文档第二小节“Excel表和名称”）。
第二步,文件内任意工作表的单元格的数据有效性设置为“=INDIRECT（“抽象属性描述”）”。
这样,“抽象属性描述”将作为Excel表返回它的值序列,进而作为数据有效性的序列的来源。
在这里注意INDIRECT函数使用的参数是字符串形式,表的名称位于两个半角双引号之间。
INDIRECT函数
INDIRECT函数的参数可以是文本（字符串）,也可以是单元格引用。
字符串参数和单元格引用参数的形式区别在于,字符串用双引号表示,或者用字符串的算式表示,单元格引用则使用形如R1C1的形式。
例如,INDIRECT（“A2”）和INDIRECT（A2）,前者的参数是一个字符串,后者的参数是一个R1C1形式的单元格引用。
前者参数中的“A2”位于一对半角双引号之间,表示使用者向函数传入的参数A2是字符串的类型。
后者没有半角双引号,表示使用者向函数传入的参数A2是单元格引用的类型。
INDIRECT函数的功能在于,利用参数,最终找到的单元格区域,进而获得区域中的数值。
下表举例展示了INDIRECT函数的用法。
使用的例子与Excel帮助文档中相同,但对公式的说明略有差异。
在这个例子的A7、A8的公式中,$A$2和$A$3单元格里存放的文本是“B2/B3”。
这时,向INDIRECT函数传入它们所在的单元格位置,分别是$A$2和$A$3,也就是向INDIRECT传入单元格引用参数的情况。
函数的计算过程是,从参数单元格取出文本“B2/B3”,再到B2和B3单元格取出数值。
此时,参数单元格中的文本“B2/B3”都是单元格引用的格式,假如不是单元格引用的格式,函数将无法得到正确结果。
虽然在第9行的例子中,A4单元格存储的文本“George”并不是直接的单元格引用的格式,但它表示一个单元格的“名称”,是另一种引用单元格的方式。
假如文档中不存在一个名为“George”的名称,那么函数就会认为参数不正确,进而返回REF错误。
而假如“George”的区域不是一个单元格而是一个序列,那么Excel会认为返回值不正确,因为只能容纳一个数值的单元格不能容纳一个数值序列,进而返回VALUE错误。
如果“单元格引用”是一个生僻的技术名词,那么它的意义是“对一个单元格区域的位置的描述”。
假如你想表示一个单元格,请使用这个单元格的列与行,例如A2或者$A$2。
假如你想表示一片单元格的区域,请使用这个区域左上角的单元格引用作为起始位置,加上这个区域的右下角的单元格引用作为结束位置,加上起始位置与结束位置之间用冒号隔开,像这样“$A$2:
$D$7”。
终止位置的行与列必须大于等于起始位置的行与列,否则没有意义。
参见Excel帮助文档“创建或更改单元格引用”、“在相对引用、绝对引用和混合引用间切换”、“对Excel表格使用结构化引用”、“在公式中定义和使用名称”。
设置二级数据有效性
再次观察名称管理器这张图,注意“抽象属性描述”中的每一个数值都是另一张表的“名称”。
也就是说,假如把第一级数据有效性设置为“=INDIRECT（“抽象属性描述”）”,那么使用者在选择的时候,无论选哪一个数值,都会选到另一张表的名称。
这时,假如在第二级数据有效性中再次使用INDIRECT函数,并且传入一级单元格引用,那么在运行的时候,INDIRECT函数就可以从有值的一级单元格中得到一个字符串值。
又因为这个字符串值恰好又是某一张二级表的名称,所以它将返回二级表的值序列。
于是可以用下面的方法来实现二级关联数据有效性:
第一步,设定一级单元格。
将一级数据有效性的序列来源设定为一级表的名称,并使一级表中的每一个值都能对应一个二级表的名称。
例如,在一级数据有效性的序列来源中输入“=INDIRECT（“抽象属性描述”）”。
第二步,设定二级单元格。
在二级有效值中使用INDIRECT函数,把一级单元格的引用作为参数传入INDIRECT函数,例如“=INDIRECT（A2）”。
如果希望自动填充数据有效性到单元格下方的单元格，请使用形如“A2”格式的单元格引用。
如果希望固定到某一个一级数据单元格，请使用形如“$A$2”的单元格引用形式。
因为Excel表所对应的单元格区域可以在任何一张工作表上,所以就实现了跨表单的二级数据有效性。
在需要增加有效性值序列的时候,只需要在相应的“Excel表”的下方输入新的数据,点击回车,使表自动扩展,就能使新的有效性序列被传递到单元格内。
在减少有效性数值序列的时候,尽管“Excel表”并不能自动减少，但是,“Excel表”的尺寸控点可以指示当前序列的范围，这样就比较不容易出错。
注意
数据有效性嵌套的层级最高是20层。
另外,可以使用数据有效性的“圈释无效数据”来检查修改有效值区域后文档中是否存在无效数据。
【Excel技巧】添加多行多列作为序列的方法
步骤/方法
1.1
首先，我们这次的要求就是将多行多列作为序列，可能大家对于单行单列做序列的方法已经是驾轻就熟了，也知道是通过数据有效性进行操作，对于多行多列，不知道大家有多少涉猎呢，或许有的人会直接想到INDIRECT函数，是的，接下来用到的就是它。
2.2
我们用常规的方法来试一下是否能够将多行多列作为序列，我们在数据有效性的来源中，选择数据区域。
3.3
点击确定后发现，这种方法是行不通的。
4.4
那我们再尝试用INDIRECT函数来试试，误区就在这里出现了。
我们发现用了INDIRECT函数也不行，难道是INDIRECT函数对于序列不能用？
哈哈，那你就错了。
5.5
接下来，小猪就介绍一个方法给你，看了以后或许会有所顿悟哦，首先，我们将A1作为数据单元格名称的替换，B1:
B4区域。
6.6
然后我们通过数据有效性，利用INDIRECT函数确定来源。
是B1到B4这个区域的数据。
7.7
这样是成功的，当然了，一列数据，序列肯定是支持的。
8.8
关键在于这一步，我们将A1单元格的B4，改为C4，让数据区域变成从B1到C4这个数据区域作为序列。
9.9
然后我们发现，序列成功了！
10.10
接下来，我们来做一些额外的测试，就是加入新的数据区域，然后再更改试试，我们加入了I,J，然后将A1中的数据区域更改。
11.11
最终我们可以发现，也是成功的，这样就实现了多行多列作为序列的效果了。
excel中怎样制作下拉菜单
步骤/方法
1.1
我以输入男女为例，打开一个Excel，我们要在性别这一列中设置一个下拉菜单，内容为“男”“女”
2.2
选中姓名后面所对着的“性别”单元格
3.3
依次打开“数据”—“有效性”
4.4
在新出来的对话框中，打开“设置”选项卡，“允许”中选择“序列，在“来源”中输入“男,女”，此处注意，“男,女”之间的逗号为英文逗号。
然后勾上“忽略空置”和“提供下拉箭头”。
点击确定即可
5.5
确定之后，你就可以看到，在“性别”这一列下，都出现了“男,女”下拉菜单
END
注意事项
∙在“来源”中输入数据时，不同数据一定要用英文逗号隔开，切不可使用中文逗号
excel如何根据身份证号计算男女性别和年龄
本例主要介绍如何在Excel中根据身份证号计算年龄和性别。
操作步骤：
1.1
首先看一下原始数据，本例以18位身份证号为例。
A列是18位的身份证号，需要在B列和C列分别提计算出年龄和性别信息。
2.2
我们知道，18位身份证号从第7位开始的8位数字代表出生日期。
出生的年份是从第7位开始的4位数字，因此，我们可以用MID函数将出生年份提取出来。
双击B2，输入公式：
=MID（A2,7,4）
3.3
要求年龄，需要用当前的年份减去身份证上的出生年份。
我们可以用YEAR函数来取得当前的年份，例如输入：
=YEAR（NOW（））就可以返回当前的年份2014。
4.4
将上述两个部分合并，就得到了B2的最终公式：
=YEAR（NOW（））-MID（A2,7,4）
5.5
下一步我们看一下如何判断男女。
18位身份证号的第17位是判断性别的数字，奇数代表男性，偶数代表女性。
首先，我们还是用MID函数将第17位数字提取出来，输入公式：
=MID（A2,17,1）
6.6
然后我们利用MOD函数（MOD函数是取余数的函数）取第17位数字除以2的余数，如果余数是0，则第17位是偶数，也就是该身份证是女性；反之，如果余数是1则说明身份证是男性。
我们嵌套IF函数如下：
=IF（MOD（MID（A2,17,1）,2）,"男","女"）
7.7
上述是适用于18位身份证号的公式，如果有15位的身份证号可以用公式升级成18位的，然后再利用上述公式计算即可。
excel怎么从身份证号中提取出生日期
本例主要介绍如何从18位和15位的身份证号中将出生日期提取出来。
操作步骤：
1.1
首先来看一下原始数据情况，A列为18位身份证号，需要将出生日期（即A2单元格中红色标注文本内容）提取到B2单元格。
2.2
由于在18位身份证号中出生日期的位置是固定的，即从第7位开始的8位数字是出生日期信息，因此，我们可以用MID函数将出生日期提取出来。
双击B2，输入公式：
=MID（A2,7,8），完成效果如下图：
3.3
这样提取出来的日期还是文本格式，也不符合我们一般习惯的日期格式，因此我们用TEXT（）函数进一步加工。
将B2公式改成：
=TEXT（MID（A2,7,8）,"#-00-00"）。
4.4
经过上一步的公式计算，单元格返回的内容看起来是更符合我们习惯的日期格式，但其实内容仍是文本，无法直接参与其他公式运算。
我们要进一步将它改成真正日期格式。
将B2的公式改成：
=--TEXT（MID（A2,7,8）,"#-00-00"）
5.5
通过上面的操作，单元格返回的内容是一个数字，我们需要通过设置单元格格式的方法改成日期格式显示。
如下图，选中单元格，鼠标右键，选择【设置单元格格式】，然后在【自定义】中输入代码：
yyyy-mm-dd，然后【确定】即可。
6.6
15位的身份证号方法类似，由于出生日期位置也是固定的，所以把公式修改成：
=--TEXT（MID（A3,7,6）,"#-00-00"）
7.7
如果既有18位也有15位的身份证号，可以将公式修改一下，同时适应这两种情况：
=--TEXT（（LEN（A2）=15）*19&MID（A2,7,6+（LEN（A2）=18）*2）,"0-00-00"）
升级会员 