excel的几项简单技巧.docxVIP

Excel的几项简单技巧 编写人：邵梓明 编写日期：2016-10-27 目录 TOC \o 1-3 \h \z \u 一、 冻结窗格 3 二、 几类基础公式的应用 3 1. if 3 2. count 4 3. countif 4 4. sumif 4 1) 用法1 4 2) 用法2 4 5. vlookup（hlookup） 4 三、 条件格式 5 1. 突出显示单元格规则 5 2. 项目选取规则 5 3. 数据条、色阶、图标集 5 四、 隔行插入和区分插入 5 1. 隔行插入 5 2. 区分插入 6 五、 下拉菜单、二级下拉菜单 8 1. 下拉菜单 8 2. 二级下拉菜单 9  冻结窗格 在日常工作中，通常会遇到大数据量的excel表格，比如说ftp下载的数据，往往需要首行或者首列始终可见，这时就需要用到冻结窗格功能了。冻结窗格功能在视图选项下，下图为具体位置。 单击冻结窗格后有三个选项，如下图： 冻结首行和冻结首列即为冻结当前sheet页的第一行或第一列，如下图： 冻结拆分窗格则是基于当前选中的行、列或单元格进行拆分，基于行列时，效果为当前选中行以上、列以左部分被冻结，而当前选中的为单元格时，则冻结当前所选单元格的以上的行和以左的列。 几类基础公式的应用 if、count、countif、sumif、vlookup（hlookup) if 对于公式判断，并根据对错返回不同的值。三个参数（公式，公式正确时的返回值，公式错误时的返回值）。 count 统计某个序列的值的数量。一个参数（一个序列）。 countif 统计某个序列中某个值的数量。两个参数（要统计的序列，统计的值）。 sumif Sumif有两种不同的用法： 用法1 求某个序列中值为一个固定值的和。两个参数（一个序列，一个固定值）这种用法用处较少。 用法2 求某个序列中值未某个固定值的参数其对应顺序的其他序列的值的和。三个参数（一个序列，一个固定值，另一个对应序列）。 vlookup（hlookup） vlookup搜索指定区域内首列的值满足条件的对应行列的单元格的值。四个参数（第一个是指定区域，第二个是指定区域第一列需要满足的条件，第三个是需要返回指定区域满足条件之后的第几列单元格的值，第四个是精确查找或模糊查找）。 vlookup为按列查找，hlookup为按行查找。 条件格式 条件格式分为突出显示单元格规则、项目选取规则、数据条、色阶、图标集和自己新建规则。 突出显示单元格规则 突出显示单元格规则是指当指定区域内的单元格符合指定的规则时，以一种特殊的字体颜色或填充颜色将此单元格标记出来。 项目选取规则 项目选取规则是指当指定区域内的单元格在本列中符合指定的规则时，以一种特殊的字体颜色或填充颜色将此单元格标记出来。 数据条、色阶、图标集 数据条、色阶、图标集是指将指定区域内的单元格内的数值排序，并给予本单元格按照渐变的原则添加指定的数据条、颜色或图标。 隔行插入和区分插入 隔行插入 首先在需要隔行插入的指定列的下一列添加如下图所示的任意数值。如下图所示： 然后选中添加的所有数值，选择开始-查找和选择-定位条件-常量或按ctrl+G-定位条件-常量，点击确定，有几个单元格被选中。如下图所示： 将鼠标光标放在选中的任一单元格上，右键-插入-整行，即可完成隔行插入，如下图： 区分插入 首先将需要区分插入的指定列复制，间隔一个单元格复制到下一列中。如下图所示： 然后开始-查找和选择-定位条件-行内容差异单元格或按ctrl+G-定位条件-行内容差异单元格，点击确定，有几个单元格被选中。如下图所示： 点击确定后将鼠标光标放在选中的任一单元格上，右键-插入-整行，即可完成隔行插入，如下图： 下拉菜单、二级下拉菜单 下拉菜单 在excel中制作下拉菜单需要用到数据-数据验证。 选择需要添加下拉菜单的单元格，单击数据验证后在设置叶签的允许选项处选择序列。 然后来源处可以选择单一行或单一列的数据，也可以手动输入,手动输入时数据与数据之间要用逗号隔开。 确定后选择的单元格后方即会出线下拉菜单。 二级下拉菜单 二级下拉菜单需要定义的辅助才能实现。 首先打开公式-定义名称,然后日次输入名称和引用位置。如下图： 或者可选择叶签中的行，列或区域。 然后依次定义名称张三，引用位置（1，2，3，4，5），李四，引用位置（6，7，8，9，10），王五，引用位置（11，12，13，14，15），之后打开名称管理器，可以查看已经存在的定义。 接下来即可开始制作二级下拉菜单。 首先选中一列数据，制作下拉菜单，来源处手写＂=人员＂，效果如