学习笔记--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+D—L
调用菜单命令的快捷键时，调用一级菜单用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+O—D调用条件格式，选择条件为“公式”，输入（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，“替换为”1234567，点击添加按钮，下次需要录入此账号时，只要输入abc，就会自动更正为1234567。
小技巧：自动求和
第一步，选中数据区域，然后“定位”（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函数公式写法：=C2>TIME(9 , , ) ,时间大于九点的表达式，三个参数分别为：小时、分钟、秒。
案例：上班时间大于9:00，就为迟到，否则为正常。在上班状态列F2单元格写公式=IF（C2>TIME(9,,),”迟到”，”正常”）
一技旁身——以“万”为单位，单元格格式设置，自定义格式为：0！.0,”“万”“元”
双击鼠标左键：公式向下复制。方法：将光标移至首个公式单元格边框右下角，当光标呈黑色小十字形时，双击鼠标左键。
案例：动态排名
RANK：由三个参数组成，分别为：用什么排名、去哪里排名、升序或降序。 “实发工资”进行排名，工资越高，名次越靠前，所以为降序。在E2单元格输入=rank后，ctrl+A调用面板，分别输入D2 、D2:D23、 0 ,即用D2在D2\"D23中进行降序排名。
训练自己用一句话翻译函数，如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单元格，就能分别得到出和进的源数据。
设计进出型表格的基本规则：办公用品管理
首先确定需要三张表：参数表、源数据表、分类汇总表。
其次由于购买和领用的东西以及流程相同，确定进和出的数据可以放在一张表中。
然后找出关键字段。排除汇总类的字段，如库存数量、季度购入、一季度等，留下与数据明细有关的，如，分类、名称、型号、单位、单价、金额（购买金额、领取金额）。初选完成后，再根据购买或领用的流程，加入日期、购买/领用、数量、部门、姓名等相关字段。
选定字段名称后，要将数据进行分类，确定哪些需要手工填写，哪些属于参数，可以做成有效性下拉选项或通过公式字段匹配。需要手工填写的只有数量、日期、单价，因为数据内容是随机的；产品大类、型号、购买/领用、部门以及姓名的数据列表几乎是固定不变的，属于参数，这些单元格应该用有效性控制；分类也属于参数，但应通过公式自动匹配得到；购买金额、领用金额则应该根据产品数量和单价而定。
产品大类和型号，部门和姓名，需要特别设计，选定了某产品大类或部门之后，对应的单元格应该提供相应的型号或姓名项。
提示库存是这类表格不可或缺的设计。当前库存应该紧挨着型号，数据由公式完成。
可以为每种型号的用品设定最大、最小库存量，做补货提示。
如何制作“参数表”？
三表结构中，源数据表记录过程，分类汇总表记录结果，参数表则为他们提供养分。凡是作为数据有效性“来源”的数据均为参数。其二是体现数据关系的清单。 实现二级有效性的方法：
首先，对产品大类和型号批量定义名称：Ctrl+Shitf+F3—指定名称选择“首行”，可按Ctrl+F3 查看。
然后，在型号列D2单元格设置数据有效性，允许类型选择“序列”，在“来源”处，写=indirect（C2）
函数OR的用法：OR（情况一，情况二）在F2设置有效性，允许类型为自定义，公式=OR(AND(E2=\"购买\，AND(E2=\"领取\"，F2<0)) 函数SUMIF，为条件求和。
三个参数分别为：单元格区域、满足条件、对应的数值。
案例：找出在D列的5#单元格，如何将与之同行的F列单元格的数值相加。 公式：=SUMIF(D2：D11，\"5#\SUMIF条件不止一个的时候需要借助“&”，制造一个辅助列。首先在A2写公式=C2&D2，在E2写公式=SUMIF（A$1：A1，A2，G$1：G1），计数库存数。 模糊条件的求和公式=SUMIF(B2：B15,\"*电池*\"，C2：C15)
总结计算当前库存所使用的技巧：首先，要制造一个复合字段，其次，要使用D$1：D1这种引用方式。