Excel 入门到精通笔记

10.23第一天

1.视图-冻结/拆分

2.排序：1）按照某一列顺序排：不用选中全列，把光标放在列中任意单元格，点排序

              2）按照某几列顺序排：排序-自定义排序-添加排序的列，设置排序顺序

              3）自定义序列：排序-自定义排序-添加排序的列，设置排序顺序选自定义序列，设置序列

3.筛选：按数字、首字、日期、单元格颜色筛选

4.表设计：套用模板设计表格，光标在表格的右下位置按Tab键，可以插入下一行，

  插入切片：表设计-插入切片，可以筛选连续多列，Ctrl多选，Delete可以删除切片

10.24第二天

5.条件格式（适用于将某一范围的数据突出显示，可选择数据大于或小于、文字等于、日期范围、重复值等）-突出显示单元格规则，选择范围设定格式

变更规格或删除规则，条件格式-管理规则-删除或变更

6.几张工作表合并计算：数据-合并计算-选择求和/平均数等，引用位置选择需要计算的表、添加，勾选首行首列便于识别数据。

7.数据透视表：数据-数据透视表，把光标放在想分析表的任一表格中或可选外部表，在数据透视表区域中选择想要分析的数据，直接拖到数据透视表区域中的行和列框下，可以完成筛选，值可以选择加总和平均数；将光标放在数据上，右键可以排序；连按两下左键可以看到资料来源。如果想要将值的加总改为平均数，选值-值字段设置-选平均数

组成群组：选中想组成的一组的数据-右键选择组合，修改组合的名称；若想按照时间设置组合，在设计-组合-步长中设置季度、天数、月等来设置组合；若想要数据分析图，设计-数据分析图

若原表中数据更新，需要在数据分析表中分析-刷新；若原表新增数据，在数据分析表-分析-更改数据源重新纳入添加的数据，或者可以将原表设置成表格-插入数据分析表，就可以将原表变为动态表格，数据分析表可以自动更新

10.26第三天

8.打印分页设置：页面布局-拖动分页线到指定位置，打印预览看是否有漏掉数据；若想要将标题打印在每一页开头，页面布局-打印标题-顶端标题行中选中标题-打印预览；若只将表格中某一部分打印，先选中打印的区域-设置打印区域就可；设置打印宽度、高度和缩放比例，可以调整打印的页数和打印的字体大小。打印前先预览。

9.页眉、页脚设置、加水印：视图-页面布局-光标点在页眉或页脚部分，可选择位置左中右-页眉页脚，设置页眉页脚；若要在页眉处添加公司logo，页脚页眉-图片-选择图片插入-设计图片格式，调整图片大小；添加水印：现将将要插入的水印（如“机密”）在新的表格中设置好字体，颜色和倾斜度，然后复制到word中，以图片储存-在页眉中间位置插入图片-设置图片格式，调整图片大小-选择图片颜色

10.函数：求总SUM(范围)；平均数AVERAGE（范围）；最大值MAX（范围）；最小值MIN（范围）；LARGE（array,k）array指的是数据范围，k是数字，指的是范围中第几大的数据；SMALL（范围，2）指的是范围中第二小的数据；

IF和IFS函数：=IF（范围条件，结果A,结果B），=IF（C7>=60,“及格”，“不及格”），

      =IFS（C7>=90,"A",C7>=80,“B”，C7<80，“C”）巢状IF：=IF（C7>=90,“A”，IF（C7>=80,“B”，“C”））

11.VLOOKUP函数：适用于“L”型查找，查找的关键字必须在第一列，=VLOOKUP(关键字，资料范围，列数，查询模式)，查询模式包括精确和模糊，TRUE表示模糊，大约符合（查找的数据并不一定是资料里相同的数据，可以是接近的，且资料数据必须以递增方式排列），FLASE表示精确，完全符合。=VLOOKUP（J4，M3:N7，2，TRUE）

绝对参照：=VLOOKUP（J4，$M$3:$N$7，2，TRUE）,锁定资料范围。操作选择带锁定范围，按键盘F4，完成锁定

错误信息：=IFERROR（要检查的公式，公式的错误信息），如=IFERROR（VLOOKUP（J4，$M$3:$N$7，2，TRUE），“查无此人”），小瑕疵：若还未输入查询信息即空白栏时，结果框还是保留上一次查询的结果，此时需要用IF函数，=IF（）

  

 此时需要用IF函数，=IF（C3=“”，“”，IFERROR（VLOOKUP（J4，$M$3:$N$7，2，TRUE），“查无此人”）），其中""表示空白

验证：资料-资料验证-设定、输入信息、错误提示。

10.27第四天

12.计数函数COUNTIFS和SUMIFS函数：COUNT函数用于计数，只适合数据型，而COUNTA函数可以对文本型、日期、数字计数，格式为=COUNT/COUNTA（A2:A14）。COUNTIF函数用于计算符合条件的储存格数目，=COUNTIF（资料范围，筛选条件），如：=COUNTIF（C2:C14,“手机”）；COUNTIFS函数可筛选同时符合多个范围的储存格数目，=COUNTIFS（范围1，条件1，范围2，条件2...）=COUNTIFS(C2:C14,F2,D2:D14,“>”&G2)，表示信用卡消费大于1000元的个数，其中&符号是连接字符和储存格的。

同样，日期范围也可以筛选，如图筛选7月份用信用卡消费的次数，或者通过指定月份的储存格来实现筛选。

 

 SUMIFS函数表示将选择范围内的数据进行加总，两个函数对比如下图。

下图表示，6月份用信用卡消费的总金额

13.定义名称、INDIRECT函数和下拉选项的设定：

1）定义名称：将储存格的范围定义成一个容易理解的名称。操作：选中想定义名称的范围-公式-定义名称-输入名称，名称管理员页面可以进行编辑、删除名称。或者可以选取范围之后，在单元格上方的左边方框中直接输入名称，然后Enter键就可以。名称完成后可以作为一个范围使用，如名称为“金额”，想要算出这个范围的加总，=SUM（金额)

2)INDIRECT函数：通过某一条件找到某一结果。

 3）下拉选项：

 设置早餐类型可选中式和西式，餐点名称中式有馒头、烧饼、包子；西式有汉堡、土司、三明治

操作：光标放在B3-数据-数据验证-设定储存格内允许为序列，来源指定中式和西式，则第一个下拉选项已经设置完成；然后设定中式和西式的名称设定，定义中式和西式餐点所代表的范围，选中餐点所在的表格-公式-从选取范围建立，勾选最左栏，确定；最后光标放在D3-数据-数据验证，设定储存格内允许为序列，来源=INDIRECT（$B$3），确定。