excel使用技巧.docx

1.设置excel单元格1级下拉菜单的3种方法（即限制单元格输入的内容）直接输入：选择要设置的单元格，如A1选择菜单栏→数据→数据工具组→数据有效性→允许中选择序列→右边的“忽略空值”和“提供下拉菜单”全部打勾→在来源下输入数据，如1,2,3,4,5,6（逗号必须为半角模式）引用不同一工作表内的数据（必须用到定义名称）如果同一工作表的某列就是下拉菜单想要的数据，如Sheet1的A1~A4数据，且其数据分别为1,2,3,4,，操作如下定义名称：菜单栏→公式→定义的名称组→点击→在名称中输入调用数据的名称（随便取）→引用位置中输入“=Sheet1!$A$1:$A$4”。也可以点击新建名称中右下角的，快速选定，多个单元格选定按住Ctrl。在需要添加下拉菜单的单元格中→选择菜单栏→数据→数据工具组→数据有效性→允许中选择序列→右边的“忽略空值”和“提供下拉菜单”全部打勾→在来源下输入所需名称即可（注：如果是对有效列表的引用，在名称前加=）引用同一工作表内的数据如果同一工作表的某列就是下拉菜单想要的数据，如Sheet1的A1~A4数据，且其数据分别为1,2,3,4,，操作如下选择要设置的单元格选择菜单栏→数据→数据工具组→数据有效性→允许中选择序列→右边的“忽略空值”和“提供下拉菜单”全部打勾→在来源下输入“= $A$1:$A$4”。也可以点击新建名称中右下角的，快速选定，多个单元格选定按住Ctrl。2.二级下拉菜单和三级下拉菜单的制作二级菜单制作：数据有效性+定义名称+indirect（$E1）函数在中填写下拉菜单内容选择A1：C5区域，菜单栏→公式→定义的名称组→点击→勾选确定选择A1：C1，点击菜单→公式→定义的名称组→点击→名称设置省份一级菜单区域区域→数据→数据工具组→数据有效性→设置→允许→序列，在来源处录入“=省份“，其他复选框勾选选择二级菜单区域→数据有效性→序列，来源处填“=indirect（$E1）”三级菜单的制作：在二级菜单的基础上，添加如下表，选择H1：K4区域，菜单栏→公式→定义的名称组→点击→勾选确定后，就完成三级菜单的制作3.二级菜单内容如何跟随一级菜单内容变化法一：创建数据在二级菜单处输入=if（A2=“A”，1），if（A2=“B”，2）)法二：index结合match使用4.MATCH函数的使用MATCH 在引用或数组中查找值：指在某个连续区域如某行或某列中查找1个值，结果为这样个值在这样个区域中的第多个位置；比如：有一列数据：A1:A7 分别时 A B C D E F G ，C1的值为F 这个时候D1输入=match(c1,a1:a7,0) 返回值就是6(F在A1:A7中的行号）5. index函数的使用INDEX函数的使用思想是为它提供一块区域(或者一个数组)1.返回单元格（1）=INDEX(A1:A5,2)将返回对单元格A2的引用（2）=INDEX({95,96,97,98,99},5)将返回值99（3）=INDEX(A1:C5,1,3)将返回对C1的引用2.返回列或行如果row_num参数为0或者缺失，那么这指示INDEX返回由column_num参数指定的整列!{=INDEX(A1:C5,,2)}将返回对单元格区域B1:B5的引用。并且相反的情况也成立，即将column_num参数设置为0或缺失将返回整行。此时，缺失的column_num参数必须包括逗号，例如：{=INDEX(A1:C5,2,)}将返回对单元格区域A2:C2的引用。3.查找假设在单元格区域A1:M200中有一个国家度量表格或列表，可以创建引用该区域的命名公式——简单地以d代表数据区域。然后，可以非常顺利地命名该数据区域的单个列。如果国家名字在第一列，那么可以创建命名公式：Country: =INDEX(d,,1)并且，如果人口在第4列，那么可以创建命名公式：Population: =INDEX(d,,4)4.和SUMPRODUCT函数一起构成求和=SUMPRODUCT( (Left(Country,1)=”U”)*Population )返回以字母U开头的所有国家的总人口。当命名公式后，下面的公式也很好：=SUMPRODUCT( (Left(INDEX(d,,1),1)=”U”)*INDEX(d,,4) )5.在不连续区域查找当处理单元格区域时，INDEX提供了第4个参数来从提供的非连续的输入区域中选择区域块。这些区域块通过整数按在输入单元格区域中出现的顺序来引用。例如：=INDEX((data1,data2,data3),,,2)返回对data2的引用。并且{=INDEX((data1,data2,data3),,1,3)}返回data3的第1列6. 和match函数一起使用，构成二级菜单跟随一级菜单建立数据库在G2建立1