EXCEL培訓资料.docxVIP

开篇问题：你是如何汇总数据的单条件求和，通常用透视表和SUMIF函数 ；两个条件求和，可以用透视表和SUMIFS；三个条件计数，可以用函数COUNTIFS和SUMIFS。 任督二脉：数据清单表与报表无合并不报表， Excel表格里有两种表，一种叫清单，一种叫报表。先清单，后报表。清单是行与行之间不能用空白行，列与列之间不能用空白列，它是纵向扩展的，不能横向扩展。1.01 技法管窥：有效性下拉框及二级有效性数据有效性制作下拉框：为了能更方便、快捷的做好表格，需要在职位这列做个有效性下拉框。List清单在当前表的操作步骤：先准备一个清单在当前表AB列。（注意：准备的清单List，建议是不放在当前表，如果放在当前表可以在很靠后的地方，常用手法是选中清单整列，然后把它的字体变成白色，但是不要把这一列隐藏）。选中C列，点击数据→数据有效性，出现一个对话框。允许选序列，来源直接选AB2单元格到AB列最后一个有字单元格。操作：把鼠标放在AB2单元格后按快捷键ctrl+shift+↓，可直接到AB列的最下面有字的单元格。如果鼠标在最下面单元格就按ctrl+shift+↑，可到达那一列最上面单元格。快捷键ctrl+shift+↑↓表示快速选取。返回，确定。这时C列就有有效性下拉框了，所以第一步选中很关键。现在要把C1单元格的有效性给去掉，就按上面步骤2的操作，出现对话框后点一下全部清除，再确定，这个时候C1的有效性就被清除了，这样C列（职位）的有效性就做好了。List清单不在当前表的操作步骤：打开List表格，选中C2到最下面有字单元格部分（C1不需要选），当前单元格应该叫C2-C37，但是这个名字过于学术化，我们需要给它起个通俗的名字。快速取名字的方法：选中单元格后，把鼠标移到上面这个名称框上，把原来的C2删除，输入职位（建议用中文、中文的全拼和英文的全称），名字输好后一定要记得回车确认，这时的C2-C37有一个新的名字叫职位。标准取名字的方法：选中单元格后，公式→名称管理器→新建，出现对话框，名称处输入职位，确定，这是标准的做法。选择F列做，选中F列，点击数据→数据有效性。设置允许序列（序列即为有下拉框的都是序列，是比较基础的，其他的内容也很多，可以自行研究），来源处输入=职位（名称一定要用等于号把它引出来）。确定，这样F列的有效性下拉框就完成了。设置性别有效性的操作步骤：选中性别列，点击数据→数据有效性。设置允许序列，来源输入男, 女，未知。需要关注下输入信息，输入信息界面有个标题，标题处填写请选择性别！，输入信息处填写男，女，未知。出错警告界面也要注意下，样式处一定要填写停止（即一旦出错误了就不让继续操作）。确定，这时会发现性别列的任何一个单元格都有提示，其他列都没有。二级联动下拉框：设置一级科目有效性的操作步骤：批量定义名称，选中范围E1-J10，点击公式→根据所选内容创建，快捷键是ctrl+shift+F3，出现对话框，创建名称首行打√。确定，出现对话框，再一直点击是，这样就定义好了。点击公式→名称管理器，出现对话框，每个名称对应的引用位置都是一样的。改动引用位置，选中需要改动的那条名称，鼠标放到引用位置处，把最后的截止位置改为那一列的有字单元格的位置。回车确认，这样依次改下去就可以了。（如图3.2.5）注意：一级科目的项目内容和所有二级科目的名称都是相匹配的，但不是一回事，是间接关系。先做一级科目，选中B列或直接选中B2-B20，点击数据→数据有效性，设置允许序列，来源=一级科目，确定。再做二级科目，选中C2-C20，（注意选择的行数必须要跟一级科目是一样的），点击数据→数据有效性，设置允许序列，来源=indirect($B2)。注意：函数indirect表示间接返回，$B2表示锁死B列，不锁行。确定。选择的时候必须是一级科目先选，二级科目才能选。1.02 技法管窥：连体数据的快速分开与合成连体数据的快速分开: 需要把上图的B列分开，分开以后应该是四个部分，就要再分出三个部分，分列是不能自动插入空白列的，要手动插入空白列，那就要在C列前插入3个空白列。分开单元格的操作步骤：选中C、D、E三列，右键插入列，这样就插入了三列空选中B列，点击数据→分列，出现对话框，文件类型选分隔符号，下一步。分隔符号选其他-，下一步。选中日期列，修改上面的列数据格式日期YMD。完成，确定替换目标单元格内容。连体数据的快速合成:分开的列需要合成记得一定要加分隔符号。合成单元格的操作步骤：在E列输入公式=A2“-”B2“-”C2“-”D2，回车。把鼠标放在E2单元格的右下角双击到底，这样可以看出所有的日期都是数字它其实代表的是某天的日期。实例：在空白单元格按快捷键ctrl+；会出现当前的日期，再选中这个日期按快捷键ctrl+shift+!就会转变成数字。在