EXCEL学习笔记.doc

学习笔记-Excel Vlookup 属于查找与引用函数，作用是：查找某单元格数据在源数据库中是否存在，如存在，则返回源数据库中同行指定列的单元格内容，如不存在，则返回#N/A。Vlookup有四个参数：用什么找？去哪里找？找到了返回第几个值？精确找还是模糊找？步骤如下1、设置参数（定位F2，输入公式）2、用什么找用E2单元格去找3、去哪里找去参数表A:D列数据区域找4、返回第几个值返回参数表A:D列第2列的值5、精确查找必须代码相同才返回匹配值公式：=VLOOKUP($E2，参数表!$A:$D,2,0)需要复制时，应该改为：公式：=VLOOKUP($E2，参数表!$A:$D,COLUMN(B1),0)COLUMN的作用是，返回单元格的列号。透视表的顺序排列求，不同省份，不同城市，不同行业在不同机构类型的主营业务收入总和。一级行字段：所在省份。二级行字段：所在地。三级行字段：行业。一级列字段：机构类型。数据项字段：主营业务收入汇总方式：求和注意：做汇总表时，两个列字段是极限，一个列字段是标准，其余的按顺序添加在行字段。求，不同企业成立月份、不同省份在不同营业状态下的年末从业人员数总和。一级行字段：企业成立时间二级行字段：所在省份一级列字段：营业状态数据项字段：年末从业人员数汇总方式：求和。设计表格步骤第一味：顾全局，背景确认，表格定性、定量、定损，明确需要做什么，做多少，怎么做，战略高度上的思考。本例定性：修改现有表格，创新新表格，重塑工作流程，设计的表格需要供多人操作。本例定量：每月十几条销售数据，几百条报价信息手工录入。销售明细表应尽量详细，报价记录表要精简字段，均可设计为一份源数据表记录多年的销售/报价数据本例定损：数据录入需规范，字段分类要清晰，业务字段要详尽。第二味：知目的，明确需求第三味：勾轮廓，字段设定字段分析、字段拓展、字段补全第四味：定结构，流程解析工作顺序、录入方式：把所有需要手工录入的字段提前，公式链接字段一次靠后，就得到了字段完整并且排列正确的源数据表。第五味：细打磨，表格装修：清晰，安全，智能，美观。清晰：检查字段名称是否带有单位，且单位是否正确。安全：单元格的数据录入安全数据有效性Alt+DL调用菜单命令的快捷键时，调用一级菜单用Alt键加上菜单名称括号里的字母，调用二、三级菜单仅敲击相应命令括号里的字母即可。完成设置后，向下复制数据有效性允许序列来源输入，或选择区域智能：运用vlookup公式。美观：舒服、直观。文本对齐，垂直方向居中对齐，文本大小10号字，字体，中文用宋体，英文用Arial或者Times New Roman，网格线，去除网格线的表格（工具-选项-视图-网格线）单元格边框，同类数据区域采用相同的边框，禁止大面积使用粗边框或虚线边框，色彩，不宜超过三种，突出标题行，简化数据区域，待输入的数据区域最好不着填充色。数据区域手工录入，复制粘贴，公式链接的数据区域要用不同的填充色区分，以告知使用者什么地方需要填写，什么地方需要复制粘贴。字体大小需要录入和经常查看的单元格字体稍大，公式链接生成的明细数据字体可以调小，以此强调表格中数据的关注重点和操作重点。边框用虚线边框弱化明细数据或非重点数据，以此突出待录入和主要关注的数据，用粗实线边框分隔录入方式不同的数据区域。工作表以不同的工作标签颜色区分汇总表，源数据表及参数表，明确地告诉使用者哪个工作表需要填写，哪个工作表仅供参考。合理运用颜色管理，可以规范表格操作，降低出错风险，提高工作效率。工作步骤：1、数据录入，2、整理数据，3、分类汇总表保持数据的连贯性，为明细数据添加新的属性，如，月份，部门，然后汇总。数据关联的思路分享1、c表b:c列-用A列数据（型号）到A表进行匹配，返回对应的min、max值（函数：vlookup）2、c表d:i列-用A列数据（型号）分别与d:i标题数据（库房名称）组合，再与B表A、C列的组合匹配，返回对应的求和值（函数：sumif）3、C表J列返回D:I列的求和值（库存总数）（函数：sum）4、C表K:N列判断J列值（库存总数）是否小于B列min值或大于C列max值，计算缺货或待出货数量（函数：if）5、C表0列用A列数据（型号）到A表进行匹配，返回对应的类别（函数：vlookup）6、C表自动填充数值的单元格，都会根据数值变化，智能化显示设定的填充色和字体颜色，以此提醒读表者关注重点数据（技能：条件格式）三种图表介绍一、对比份额，分布等信息时，可以用饼图。它可以很直观地通过扇面大小，来说明比例关系，而不强调数值本身。二、比较数值时，用柱形图，既关注多个数值间的大小关系，也关注单个数值本身。三、表示趋势时，用折线图，它指明各个时期的绝对值，但关注焦点在变化趋势上。干净：图表越花俏，越没有重点，只有保持背景和色彩的干净，才能突出焦点；原配：默认的图表类型几乎都是最好的，不用再尝试其他；大小：图大、字小，就能突出图形特色，不要让文字影响图表的可读性；繁简：简洁很重要，你可以在图表上显示更多信息，不过，这代表你想毁了它。天下第一表的源数据表必须具备以下几个条件：第一，一维数据，即，只有顶端标题行，没有左端标题咧；第二，一个顶端标题行，即，只有第一行是标题（字段），从第二行开始就是数据；第三，没有合并单元格，即，不能出现任何形式的合并单元格；第四，连续的数据，即，数据区域中不能出现空白单元格、空白行，以及空白列；第五，准确的数据内容，即，大致包括完整的字段、一致的描述，以及分列记录的数值与单位。生成工资条方法：第一步，对K2和K3分别输入1和3，选中他们，当光标在K3单元格右下角呈现黑色十字形时，双击鼠标左键向下复制到与J列最后一个非空单元格同行。第二步，在K24和K25分别输入2和4，选中他们并移动光标使其变成黑色十字，然后按住鼠标左键将其下拉至数字超过奇数列的倒数第二个数41.第三步，对K列按“升序”排序，完成空白行的插入。批量添加标题行（批量录入技巧）第一步，选中A1：J44，即包含空白行的所有工资明细，按F5，选择“空值”为定位条件。第二步，直接输入=A1（确保已经选中了工资明细间的所有空白行，且公式输入在A3单元格）第三步，按Ctrl+Enter完成批量录入，于是，工资条就制作成功了。案例：面试提醒表制作公式：=IF(LEN($A2)=0,VLOOKUP($A2，应聘者明细表！$A:$M,COLUMN(B1),0)公式设置完成后，在A列录入多个应聘编号，就能瞬间得到所有其他相关信息。中文的当天日期公式：=TODAY()选中初试日期列K2单元格，按Alt+OD调用条件格式，选择条件为“公式”，输入（K2-TODAY()=1）;设定待显示的单元格底纹为黄色，点“确定”完成。然后使用格式刷复制到其他单元格。小技巧一：乾坤大挪移第一步，选中待调整列，将光标移至该列左右任意一侧边缘，呈四向箭头形状。第二步，按住shift键不放，拖动鼠标至待插入位置，松开鼠标左键完成。小技巧二：删除空白列第一步，复制所有数据；第二步，在待粘贴处，右键点选“选择性粘贴”，勾选“转置”并确定；第三步，在任意列筛选“空白”并删除所有空白行；第四步，再次使用数据“转置”完成。 小技巧三：单元格一键批量录入第一步，选定多个单元格；第二步，什么都不要动，直接敲击键盘输入内容；第三步，按住ctrl键按enter键。 小技巧四：取消合并单元格第一步，全选数据第二步，点击“合并并居中”按钮，拆分合并单元格；第三步，按F5调用“定位”功能，设定“定位条件”；第四步，选中“空值”为定位条件，点确定；第五步，直接输入“=B3”（因为B4空白单元格应该填充B3的数据内容，所以输入=B3）（输入的内容总是为当前单元格的上一个单元格坐标）第六步，ctrl+enter，ok！（运用选择性粘贴将公式转换成文本） 小技巧五：没有中文的中文大写数字第一步，输入阿拉伯数字第二步，设置单元格格式（ctrl+1），选中“数字”标签下“特殊”中的“中文大写数字”；注意，小数部分无法正确转换 小技巧六：条件格式任务：将数值小于60的单元格，填充为红色底纹第一步，选中数据，调用“格式”菜单里的“条件格式”命令；第二步，设置条件为单元格数值小于60第三步，设置格式为红色单元格底纹，点确定注意，条件格式是一种格式，不是用ctrl+c，而是用格式刷进行复制。 小技巧七：快速输入当日日期及时间一、Ctrl+；日期二、Ctrl+Shift+；时间 小技巧八：快速选定非空单元格任务：选中B:D列所有非空单元格。首先选中B1：D1，然后同时按住Ctrl+Shift，再按方向键，瞬间完成！ 小技巧九：保护工作表第一步，选择允许录入的单元格区域，设置单元格格式，取消勾选保护标签中的“锁定”；第二步，调用保护工作表功能，取消勾选“选定锁定单元格”，点击确定完成。 小技巧十：自动更正选项打开“工具”菜单的“自动更正选项，在“替换”栏输入abc，“替换为”123456789，点击添加按钮，下次需要录入此账号时，只要输入abc，就会自动更正为123456789。 小技巧：自动求和第一步，选中数据区域，然后“定位”（F5）到所有空值第二步，按下Alt+=，也就是自动求和，你会看到所有的汇总瞬间全部完成。 技巧二：工作表间的快速切换：Ctrl+PgDn：，切换到下一个工作表，Ctrl+PgUp，切换回来。技巧二：快速生成序号列：第一步：在A2，A3单元格分别输入数字1和2，并选中这两个单元格；第二步：将光标移至A3单元格右下角，使其呈黑色十字形，然后双击鼠标左键完成序列的填充。技巧三：填“空”式复制。第一步，选中G3：G12，并复制第二步，选中F3，单击鼠标右键，点击“选择性粘贴”，勾选“跳过空单元格”，点击确定。F2：使单元格进入编辑状态的快捷键，同时也是在界面中，修改Excel文档名称的快捷键操作。使用方法：选择待编辑的单元格，按F2进入。F4：使单元格的引用类型在相对、绝对、混合引用中循环切换，就是快速添加或删除“$”符号。使用方法：抹黑单元格引用，按F4切换。F4：一键切换引用类型，反复按F4，引用类型就会从绝对引用开始，进行循环切换。F9：使Excel进行一次计算，对象可以是引用的数据区域（A2:A5）、函数（RAND()）或公式（=A1+B1）。Ctrl+H ：调用替换对话框Ctrl+F ：调用查找对话框Enter ：光标下移Shift+Enter ：光标往上Tab ：光标往右Shift+Tab ：光标往左Ctrl+Enter ：原地不动Ctrl+1 ：调出单元格格式设置对话框ctrl+A：在知道函数名称的情况下，调用函数参数对话框面板。方法：输入“=函数名称”后，按ctrl+A。Shiht+F3 ：相当于点击fx,一是从函数列表中选择函数，二是对函数的参数进行修改。方法：将光标插入该函数范围，按Shift+F3.Ctrl+Z ：撤销。出错时，按回车，然后按Ctrl+Z 将公式恢复原样。Ctrl+ :将公式单元格从显示结果切换为显示公式，进行检查及修改。Ctrl+O:打开文件Alt+O-D ：调用“条件格式”时间TIME函数公式写法：=C2TIME(9 , , ) ,时间大于九点的表达式，三个参数分别为：小时、分钟、秒。案例：上班时间大于9:00，就为迟到，否则为正常。在上班状态列F2单元格写公式=IF（C2TIME(9,),”迟到”，”正常”）一技旁身以“万”为单位，单元格格式设置，自定义格式为：0！.0,”“万”“元”双击鼠标左键：公式向下复制。方法：将光标移至首个公式单元格边框右下角，当光标呈黑色小十字形时，双击鼠标左键。案例：动态排名RANK：由三个参数组成，分别为：用什么排名、去哪里排名、升序或降序。 “实发工资”进行排名，工资越高，名次越靠前，所以为降序。在E2单元格输入=rank后，ctrl+A调用面板，分别输入D2 、D2:D23、 0 ,即用D2在D2D23中进行降序排名。训练自己用一句话翻译函数，如vlookup是用一个数据去一堆数据里面找并返回同行指定的数据；if是当条件满足时返回真值，否则返回假值。数据有效性：作用是，只有当单元格内符合预设的条件时，才允许录入，否则阻止录入完成并报错。调用路径是：数据有效性，Alt+D+L当选择“序列”时，编辑“来源”的方法：手工输入，用半角的逗号隔开，或者选择数据区域。整数：控制财务报表中的金额或收发货物的数量，使其不超过实际的数量级；日期：控制报表中不出现错误的日期格式，以及不必要的过去或未来的日期；文本长度：控制如合同号、序列号、手机号等数据，使录入内容的位数一致。案例：今日事今日毕具体操作：选中待录入日期的单元格区域，调出数据有效性设置面板，在“允许”中选择“自定义”，然后在公式输入=A2=TODAY(),点击确定。案例：数据依次录入具体操作：选中待录入数据的单元格区域，调出数据有效性设置面板，在“允许”中选择“自定义”，然后在“公式”输入=LEN（A1）0 ,(A2为当前激活单元格)，最后，取消勾选“忽略空值”，点击确定。思路：当上一个单元格有数据时，才允许录入，以此确保不会空格、跳行。设置双条件：在B2的有效性公式中输入=AND(LEN(A2)0，LEN(B1)0).条件格式案例：标出每门学科的最高分调用格式菜单Alt+O-D，选择公式，输入=B2=MAX(B$2：B$22)，点击格式设置界面，设置需要显示的格式，最后确定。条件格式是一种格式，复制格式要用格式刷。COUNTIF:有两个参数，分别为：在什么数据区域进行计数、计数的条件是什么。标出至少三门学科成绩大于85分的学生。=COUNTIF($B1:$D1,85)=3一技旁身：批量删除有空单元格的行首先，选定包含所有空单元格的数据区域，定位F5空值，点击右键选择删除，在弹出的对话框中选择整行。用键盘调用菜单功能的按键式Alt，它可以使光标进入菜单选项。然后按照功能按钮旁边提示的字母依次敲击，就能实现各种功能的调用，如20003版：Alt+D+F+F自动筛选。“分手”方法一：筛选法。复制可见单元格，在复制前需增加一步，选中数据后，按Alt+；（分号）定位“可见单元格”。“分手”方法二：查看透视表明细法。双击B5、B6单元格，就能分别得到出和进的源数据。设计进出型表格的基本规则：办公用品管理首先确定需要三张表：参数表、源数据表、分类汇总表。其次由于购买和领用的东西以及流程相同，确定进和出的数据可以放在一张表中。然后找出关键字段。排除汇总类的字段，如库存数量、季度购入、一季度等，留下与数据明细有关的，如，分类、名称、型号、单位、单价、金额（购买金额、领取金额）。初选完成后，再根据购买或领用的流程，加入日期、购买/领用、数量、部门、姓名等相关字段。选定字段名称后，要将数据进行分类，确定哪些需要手工填写，哪些属于参数，可以做成有效性下拉选项或通过公式字段匹配。需要手工填写的只有数量、日期、单价，因为数据内容是随机的；产品大类、型号、购买/领用、部门以及姓名的数据列表几乎是固定不变的，属于参数，这些单元格应该用有效性控制；分类也属于参