第一课：Excel基础
一、Excel简介和工作介面
Excel是微软公司出品的Office系列办公软件中的一个组件，确切地说，它是一个电子表格软件，可以用来制作电子表格、完成许多复杂的数据运算，进行数据的分析和预测并且具有强大的制作图表的功能。Excel电子表格可以容纳庞大的数据，一个工作表最多有65536行×256列
二、我要处女作——销售表
三、数据修改和自动填充
1、数据修改
◎全部修改：定位要修改数据的单元格，重新输入数据，将覆盖原来的数据。◎局部修改：双击单元格，按F2键，在编辑栏修改等方法可修改部分数据
2、自动填充：
◎把光标置于单元格的右下角，按左键拖动鼠标，即可以进行相同数据填充或序列填充。
四、自动填充的类型
◎[编辑]→[填充] →[序列] →[等差序列]
◎[编辑]→[填充] →[序列] →[等比序列]
◎[编辑]→[填充] →[序列] →[日期]
第二课：Excel的设置一、Excel格式工具栏
二、“单元格格式”对话框——“数字”面板：
作用：用于设置数据的格式。
三、单元格对话框——“对齐”面板：
四、单元格对话框——“字体、边框、图案”面板：
五、条件格式和查找
1、条件格式：为符合某些条件的单元格设置特定的格式。
◎[格式]—[条件格式]
2、查找：
◎[编辑]—[查找] Ctrl+F
（注：查找的范围是当前工作表，不是当前工作簿）
第三课：编辑工作表
一、调整行高、列宽
◎方法一：定位单元格，[格式]→[行]→[行高]
[格式]→[列]→[列宽]
◎方法二：用鼠标直接拖动行号或列标之间的线。
◎方法三：用鼠标左键双击行号或列标之间的线，产生最适合的行高或列宽。
二、插入、删除行列及隐藏、显示行列
1、插入行列
◎定位单元格，[插入]→[行]／[列]（或用右键快捷菜单）
2、删除行列
◎定位单元格，[编辑]→[删除]（或用右键快捷菜单）
3、隐藏、显示行列
◎定位单元格，[格式]→[行] →[隐藏]\[取消隐藏]
[格式]→[列] →[隐藏] \[取消隐藏]
（注：可把光标定位在“行号”或“列标”，然后右击鼠标，选择“隐藏”）
三、移动和复制行列
1.移动行列
◎选定行／列，[剪切]→[选定目标行／列]→[粘贴]
注：如不想覆盖目标行／列，请右击，选择“插入复制单元格”
2.复制行列
◎选定行／列，[复制]→[选定目标行／列]→[粘贴]
第四课：工作簿管理
一、工作表的“插入、删除和重命名”
1.插入工作表
◎[插入→工作表]
（注：可在工作表标签上右击，选择“插入”）
2.删除工作表
◎[编辑]→[删除工作表]
（注：可在工作表标签上右击，选择“删除”）
3.重命名工作表
◎[格式]→[工作表]→[重命名]]
（注：可在工作表标签上右击，选择“重命名”）
（注：在工作表标签上右击，还可以选择“工作表标签颜色”）
（注：拖动工作表标签，还可以移动工作的次序）
二、工作表的“隐藏和显示”及工作表的“移动和复制”
1.隐藏工作表（可把一些暂时用不上，但又不能删除的工作表隐藏起来）◎[格式]→[工作表]→[隐藏]
2.显示工作表
◎[格式]→[工作表]→[取消隐藏]
3.移动或复制工作表
◎[编辑]→[移动或复制工作表]
（注：[建立副本] 打“√”表示复制，不打“√”，表示移动。）
（注：也可在工作表标签上右击，还可以选择“移动或复制工作表”）三、冻结窗格
◎冻结窗格：定位活动单元格→[窗口]→[冻结窗格]。
◎撤消冻结窗格：[窗口]→[撤消冻结窗格]。
四、工作表的保护
1.设置打开权限密码
◎[工具]—[选项]—[安全性]
（注：设置密码后，要“保存”一次）
2.保护工作表
◎[工具]—[保护]—[保护工作表]
◎[工具]—[保护]—[撤消工作表保护]
3.保护工作簿
◎ [工具]—[保护]—[保护工作簿]
◎[工具]—[保护]—[撤消工作簿保护]
第五课：公式和函数
一、使用“公式法”来计算
◎步骤：
①把光标定位在要放置结果的位置
②以“=“号开头
③输入公式后，接回车键确定
（注：公式法很灵活，可进行各种“四则混合运算”，）
（注:运算的顺序也是按“四则混合运算”,如先算括号,后乘除,后加减）二、使用“自动求值法”来计算
把光标定位于要放结果的位置,然后点标工具栏的自动求值按钮.
(注：“自动求值”的方法比较方便，但7缺点是不够灵活)
三、使用“函数法”来计算
1.概念：Excel函数是预先定义、执行计算、分折等处理数据任务的特殊公式。
2.函数的组成：= sum (参数1,参数2)
?常用的函数
?求和函数：sum
?求平均值函数：average
?求最大值函数：max
?求最小值函数：min
?计数函数：count
?求乘积函数：product
第六课：数据清单操作
一、数据的排序
◎[数据]→[排序]…，设定主要、次要、第三关键字。
二、数据有效性
◎[数据]→[有效性]…
三、数据的筛选
1、自动筛选
◎[数据]→[筛选]→[自动筛选] （字段名上会出现下拉箭头）。
（注：再次执行命令，能清除自动筛选操作。）
2、高级筛选
◎[数据]→[筛选]→[自动筛选] （要预先确定筛选条件）。
四、分类汇总。
◎[数据]→[分类汇总]
(注：分类汇总前，一般要对以“分类字段”作为关键字来对数据进行排序)
第七课：创建图据图表
一、创建数据图表。
步骤:
①、选择数据源（即要用于创建图表的表格）
②、执行[插入]→[图表]命令。（或单击“常用工具栏”的图表按钮）
③、在“图表选项”中可输入图表标题、分类标题、数值标题等，单击“完成”。
二、修改图表的样式
1、图表格式设置：双击图表的各个对象，即可弹出“数据系列格式”对话框。
三、修改图表的数据（以矩形图表为例）
◎删除行数据：在图表中点击选中某一系列的柱子，然后按键盘的“Delete”键◎增加行数据：在表格中选择要添加的那一行数据，拖动到图表中。
◎删除列数据：在图表中，右击→[源数据] →重新选一次表格的数据。
◎增加列数据：在表格中选择要添加的那一列数据，拖动到图表中。
第八课：Excel打印设置
一、打印预览
◎[文件]—[打印预览]…..[关闭]
(注：Excel工作表是一个65536行×256列的大表格，很难确定一张A4纸的打印范围，可以先“打印预览”一次，然后关闭预览，工作表中即会出现几条虚线来显示出打印的范围。)
二、页面设置：◎[文件]—[打印预览]—[设置]
1、“页面”选项卡：设置打印的纸张大小，方向和打印内容的缩放。
2、“页边距”选项卡：设置正文的页边距，页眉、页脚的页边距，打印内容的居中方式。
3、“页眉/页脚”选项卡：设置页眉、页脚的内容。
4、“工作表”选项卡：设置打印的网格线、行号和列标等。
三、“打印”对话框
◎[文件]—[打印]—
四、设置打印区域（用于只想打印某一部分数据的时候）
1.设置打印区域
◎选择要打印的区域，[文件]—[打印区域]—[设置打印区域]
2.取消打印区域
◎[文件]—[打印区域]—[取消打印区域]
清洗处理类：3-10 关联匹配类：1-2，11-14 1）Vlookup() VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。 VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配） vlookup就是竖直查找，即列查找。通俗的讲，根据查找值参数，在查找范围的第一列搜索查找值，找到该值后，则返回值为：以第一列为准，往后推数查找列数值的这一列所对应的值。 以后几乎都使用精确匹配，最后项的参数一定要选择为false。 需求：A分销商需要7/31销量数据 ） 操作：在I 3 单元格输入：=VLOOKUP(H3,$A$3:$F$19,5,FALSE) 分析： H3为我们想查找的型号，即iphone5。为什么要写H3，而不是直接写iphone5，因为方便公式进行拖拽填充，以及保证准确性。 $A$3:$F$19为我们需要在此范围内做查找，为什么要加上绝对引用呢，因为下面的ip4和剩余的查找都引用这个区域，即我们的数据源，加上了绝对引用后，就可以对公式进行直接的拖拽。 5 从我们的数据源第一列起，我们要查询的7/31号的销量在我引用的第一列（即型号列）后面的第五列。注意这里的列数是从引用范围的第一列做为1，而不是以A列作为第一列，万万注意此处。
2）Lookup() 1 矢量形式的LOOKUP 矢量形式的LOOKUP 在一行或一列区域（称为矢量）中查找值，然后返回另一行或一列区域中相同位置处的值。如果要指定其中包含要匹配的值的区域，请使用这种形式的LOOKUP 函数。 矢量形式的语法 LOOKUP(lookup_value,lookup_vector,result_vector) Lookup_value 是LOOKUP 在第一个矢量中搜索到的值。Lookup_value 可以是数字、文本、逻辑值，也可以是代表某个值的名称或引用。 Lookup_vector 是一个仅包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。 重要说明：lookup_vector 中的值必须按升序顺序排列。例如，-2、-1、0、1、2 或A-Z 或FALSE、TRUE。否则，LOOKUP 返回的值可能不正确。大写和小写文本是等效的。 Result_vector 是一个仅包含一行或一列的区域。它的大小必须与lookup_vector 相同。 从A1:A11找D2（3），返回同行的C列的值a 注意： 如果LOOKUP 找不到lookup_value，它会匹配lookup_vector 中小于或等于lookup_value 的最大值。 A列找不到6 就返回A列5同行的 C列值g 如果lookup_value 小于lookup_vector 中的最小值，则LOOKUP 会返回#N/A 错误值。
EXCEL常用函数使用整理归类 EXCEL中常用函数的使用 1、求和函数: =SUM(区域或单元格,……) 2、条件式求和函数: =SUMIF(条件区域，条件，求和区域) 3、多重条件求和函数: =SUMIFS(求和区域，条件区域1，条件1，条件区域2，条件2，……) 4、求最 大值函数: =MAX(区域或单元格,……) 5、求最小值函数: =MIX(区域或单元格,……) 应用举例求选手的最后得分: =(SUM(D2:D8)-MAX(D2:D8)-MIN(D2:D8))/6 6、四舍五入函数: =ROUND(单元格或表达式或函数，保留小数位数) 如:=ROUND($E3/30/8,0)*1.5*$F3 7、取整函数:(不是四舍五入而是直接去掉小数) =TRUNC(单元格或表达式或函数) 8、排名函数: =RANK(单元格，单元格所在区域，0) 9、还贷款额函数: =PMT(月利率，偿还期限，贷款总额) 可求出每月的还款额 10、开平方函数: =SQRT(单元格数字)
11、数组公式: (1)计算单个结果: =SUM(F2:F17*G2:G17)+ CTRL+SHIST+ENTER (一一对应分别乘起来后求和) (2)频率分布函数: =FREQUENCY(数据区域，频率点区段)+CTRL+SHIST+ENTER。注:输入函数前先需选定要 生成频率的区域。 12、求平均数函数: =AVERAGE(区域或单元格,……) 13、条件式求平均函数: = AVERAGEIF(条件区域，条件，平均区域) 14、多重条件求平均函数: = AVERAGEIFS(求平均区域，条件区域1，条件1，条件区域2，条件2，……) 15、统计个数函数: =COUNT(区域或单元格,……) 16、条件式统计个数函数: = COUNTIF(统计区域，条件) 17、多重条件统计个数函数: = COUNTIFS(条件区域1，条件1，条件区域2，条件2，……) 实际应用举例: 及格率公式:=(COUNTIF(C2:C59,">=60")/COUNT(C2:C59)); 优秀率公式:=(COUNTIF(C2:C59,">=80")/COUNT(C2:C59)); 语文及格率公式:=COUNTIFS(语文,">=90",班级,A8)/COUNTIFS(语文,">0",班级,A8) 90分以上人数公式:=COUNTIF(C2:C59,">=90");
第一课 一、Excel2010简介与界面组成 1、EXCEL的功能：制作表格、快速录入数据、计算以及对数据进行分析与管理。 2、EXCEL的应用：办公、财务、统计、科学计算 3、EXCEL窗口的组成。 二、基本概念 1、基本概念： A、行号(每一行前面的数字) B、列标（每一列上面的字母） C、单元格（用列标加行号表示单元格的名称） D、工作表 注：默认名称是SHEET1、SHEET2……，一张工作表有1048576行和16384列组成） E、工作簿（一般有三个工作表组成，其个数可以改变，最多有255张工作表） 三、操作技巧： 1、CTRL+向下光标键：选择最后一行。 2、CTRL+向上光标键：选择第一行。 3、CTRL+向左光标键：选择第一列。 4、CTRL+向右光标键：选择最后一列。 四、文件的保存与加密 1、文件——信息——保护工作簿——用密码进行加密 2、在EXCEL中，文件名就是工作薄名。 第二、三课 一、数据的录入 1、直接输入 2、双击单元格 3、编辑栏 二、数据类型 1、数值 （1）整数 A、首尾不能为0。 B、位数不能超过11位，如果超过11位，以科学计数法方式显示。 （2）负数：在数值前加负号，或用括号将数值括起来。 （3）小数：直接输入 （4）百分数：直接输入 （5）分数： A、前面要输入”0空格” B、自动化简 C、假分数会自动转换成带分数。 注：数值型数据，自动右对齐。
2、日期和时间 （1）日期：中间加”/ ”或”—“ （2）时间：中间加： 注：日期和时间默认自动右对齐 3、文本 （1）中文： （2）英文： （3）数值型文本 A、开始——数字——常规 B、单引号+数字（注：单引号为英文输入法状态下的单引号） 注：文本默认自动左对齐 三、数据的修改与单元格选定技巧 1、：双击单元格后就可以修改了 2、连续单元格的选择 （1）直接拖动 （2）选择一个单元格后按住shift键在最后一个要选择的单元格单击。 3、不连续单元格的选择 （1）按ctrl键单击要选择的单元格 4、行与列的选择 （1）单击行号选择一行，单击后按住鼠标拖动可以选择多行。 （2）单击列标选择一列，单击后按住鼠标拖动可以选择多列。 5、整张工作表的选择 （1）在行号与列标交界处单击 （2）Ctrl+A：全选 注：光标处于空白处，否则选择的是数据区域 四、移动数据 1、剪切——粘贴 2、将鼠标放在单元格边缘直接拖动 五、复制数据 1、将鼠标放在单元格边缘，按住Ctrl键拖动 六、删除数据 1、delete键：仅删除内容，不会删除格式。 2、开始——编辑——清除 七、数据的自动填充 1、字符填充： （1）拖动填充句柄 （2）A、选定区域B、录入数据C、按Ctrl+回车 2、序列填充： （1）非数字序列（如：日期和星期等） A、默认是序列方式，但可以根据实际情况进行选择。（自动填充选项） B、Ctrl+拖动：以复制方式进行填充。 （2）数字序列 A、默认是复制。 B、Ctrl+拖动：以序列方式填充。（和非数字序列方式正好相反）
15个常用的Excel函数公式，拿来即用 1、查找重复内容 =IF(COUNTIF(A:A,A2)>1,"重复","") 2、重复内容首次出现时不提示 =IF(COUNTIF(A$2:A2,A2)>1,"重复","") 3、重复内容首次出现时提示重复 =IF(COUNTIF(A2:A99,A2)>1,"重复","")
4、根据出生年月计算年龄 =DATEDIF(A2,TODAY(),"y") 5、根据身份证号码提取出生年月 =--TEXT(MID(A2,7,8),"0-00-00") 6、根据身份证号码提取性别 =IF(MOD(MID(A2,15,3),2),"男","女") 7、几个常用的汇总公式 A列求和：=SUM(A:A)
A列最小值：=MIN(A:A) A列最大值：=MAX (A:A) A列平均值：=AVERAGE(A:A) A列数值个数：=COUNT(A:A) 8、成绩排名 =RANK.EQ(A2,A$2:A$7) 9、中国式排名（相同成绩不占用名次） =SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1 10、90分以上的人数
=COUNTIF(B1:B7,">90") 11、各分数段的人数 同时选中E2:E5，输入以下公式，按Shift+Ctrl+Enter =FREQUENCY(B2:B7,{70;80;90}) 12、按条件统计平均值 =AVERAGEIF(B2:B7,"男",C2:C7) 13、多条件统计平均值 =AVERAGEIFS(D2:D7,C2:C7,"男",B2:B7,"销售")
常用清洗处理类函数 1、Trim 清除掉字符串两边的空格，将格式转化为文本，如需转为数值则=VALUE(TRIM(单元格))。substitute对指定的字符串进行替换，可去掉所有空格，=substitute(text,old_text,new_text,[instance_num])=substitute（需要替换的文本，旧文本，新文本，第N个旧文本） 2、Concatenate，=Concatenate(单元格1，单元格2……)合并单元格中的内容，也可以用&&&。 3、Replace=Replace（指定字符串，哪个位置开始替换，替换几个字符，替换成什么）替换掉单元格的字符串，清洗使用较多。如新文本为文本格式，需加“”且在英文状态下输入。 4、Left／Right／Mid =Mid(指定字符串，开始位置，截取长度) 5、Len／Lenb 返回字符串的长度，在len中，中文计算为一个，在lenb中，中文计算为两个。Find=Find（要查找字符，指定字符串，第几个字符） 查找某字符串出现的位置，可以指定为第几次出现，与Left／Right／Mid结合能完成简单的文本提取 6、Search和Find类似，区别是Search大小写不敏感，但支持＊通配符 7、Lookup =Lookup（查找的值，值所在的位置，返回相应位置的值） 最被忽略的函数，功能性和Vlookup一样，但是引申有数组匹配和二分法。 8、Index＝Index（查找的区域，区域内第几行，区域内第几列）和Match组
合，媲美Vlookup，但是功能更强大。 Match＝Match（查找指定的值，查找所在区域，查找方式的参数） 和Lookup类似，但是可以按照指定方式查找，比如大于、小于或等于。返回值所在的位置。 9、Row返回单元格所在的行；Column返回单元格所在的列； 每个季度合计行的余数都为1，利用这个特性进行跨行求和。 输入公式=SUM(IF(MOD(ROW(B2:B17),4)=1,B2:B17)) 10、Offset＝Offset（指定点，偏移多少行，偏移多少列，返回多少行，返回多少列）建立坐标系，以坐标系为原点，返回距离原点的值或者区域。正数代表向下或向右，负数则相反。 逻辑运算类 1、IF、And、Or、IS系列 计算统计类 1、Sum／Sumif／Sumifs，统计满足条件的单元格总和。 2、Sumproduct统计总和相关，=sumproduct（A2:A8,B2:B8）可转化为 =sumproduct（数组1，数组2）=sumproduct（｛1；2；3；4；5；6；7｝，｛1；2；3；4；5；6；7｝）=1*1+2*2+3*3+4*4+5*5+6*6+7*7=140。 3、Count／Countif／Countifs统计满足条件的字符串个数 4、Max返回数组或引用区域的最大值；Min返回数组或引用区域的最小值；Rank排序，返回指定值在引用区域的排名，重复值同一排名。 5、Rand／Randbetween常用随机抽样，前者返回0~1之间的随机值，后者可以指定范围。
EXCEL中常用函数及使用方法 Excel函数一共有11类：数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。 1.数据库函数 当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。例如，在一个包含销售信息的数据清单中，可以计算出所有销售数值大于1,000 且小于2,500 的行或记录的总数。Microsoft Excel 共有12 个工作表函数用于对存储在数据清单或数据库中的数据进行分析，这些函数的统一名称为Dfunctions，也称为D 函数，每个函数均有三个相同的参数：database、field 和criteria。这些参数指向数据库函数所使用的工作表区域。其中参数database 为工作表上包含数据清单的区域。参数field 为需要汇总的列的标志。参数criteria 为工作表上包含指定条件的区域。 2.日期与时间函数 通过日期与时间函数，可以在公式中分析和处理日期值和时间值。 3.工程函数 工程工作表函数用于工程分析。这类函数中的大多数可分为三种类型：对复数进行处理的函数、在不同的数字系统（如十进制系统、十六进制系统、八进制系统和二进制系统）间进行数值转换的函数、在不同的度量系统中进行数值转换的函数。 4.财务函数 财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。财务函数中常见的参数： 未来值(fv)--在所有付款发生后的投资或贷款的价值。 期间数(nper)--投资的总支付期间数。 付款(pmt)--对于一项投资或贷款的定期支付数额。 现值(pv)--在投资期初的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。 利率(rate)--投资或贷款的利率或贴现率。 类型(type)--付款期间内进行支付的间隔，如在月初或月末。 5.信息函数 可以使用信息工作表函数确定存储在单元格中的数据的类型。信息函数包含一组称为IS 的工作表函数，在单元格满足条件时返回TRUE。例如，如果单元格包含一个偶数值，ISEVEN 工作表函数返回TRUE。如果需要确定某个单元格区域中是否存在空白单元格，可以使用COUNTBLANK 工作表函数对单元格区域中的空白单元格进行计数，或者使用ISBLANK 工作表函数确定区域中的某个单元格是否为空。 6.逻辑函数 使用逻辑函数可以进行真假值判断，或者进行复合检验。例如，可以使用IF 函数确定条件为真还是假，并由此返回不同的数值。
清洗处理类：3-10 关联匹配类：1-2, 11-14 1) Vlookup() VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。 VLOOKUP (查找值，查找范]韦I ，查找列数，精确匹配或者近似匹配) vlookup 就是竖直查找，即列查找。通俗的讲，根据查找值参数，在查找范围的第一列搜索 查找值，找到该值后，则返回值为：以第一列为准，往后推数查找列数值的这一列所对应的 值。 以后几乎都使用精确匹配，最后项的参数一定要选择为false 。 需求：A 分销商需要於1销量数据 操作:在 13 单元格输入：二VLOOKUP(H3,$A$3:$F$19,5,FALSE) 分析： H3为我们想查找的型号，即iphone5o 为什么要写H3,而不是直接写iphone5,因为方 便公式进行拖拽填充，以及保证准确性。 $A$3:$F$19为我们需要在此范围内做查找，为什么要加上绝对引用呢，因为下面的ip4 和剩 余的查找都引用这个区域，即我们的数据源，加上了绝对引用后，就可以対公式进行直 接的拖 拽。 5从我们的数据源第一列起，我们要查询的7/31号的销量在我引用的第一列(即型号 列)后面的第五列。注意这里的列数是从引用范围的第一列做为1,而不是以A 列作为第一 列，万万注 意此处。 iphoneS iM ip4ff 乐 phgb 小初 100 91 62 73 91 50 61 52 98 97 100 59 曲 82 63 $0 69 52 77 51 50 81 96 &0 67 $0 8) $5 62 3 100 52 86 57 53 50 84 79 60 78 20 “ 71 92 100 !5 91 70 69 曲 28 SO 67 89 62 乂 69 60 69 73 18 70 63 87 ?) 58 79 77 65 50 82 脯 &8 73 SO 79 100 67 28 趺 81 57 n W ： ShMtl/a?et2： Sheets . j-MkA- 护 :S B W “i 町? ? 百厦轻稔E 用乂各八Microsoft Ercei ? 7 >?；■ ■■■■?■■ ■ r ■ ■■ ■ i ?■ ■ i ■■ ■? ― —— ■?■ ■ ■ -7 ttA 55BB^? 公式 8? 古竟 fiffi ffStlJI Hl 7 note2 H? 900 800 700 600 20202020 14 15 16 20 w tX ：ik4?4d 源数据 分销商需要数据
EXCEL笔记 第一讲：认识EXCEL 1.名称框的快捷查找、选取作用 可在名称框中输入A10000，即可直接找到A10000单元格；如果输入A1：C10000，即可直接选取A1：C10000区域。 2.直接到表格最后一行快捷键 CTRL+END 3.单元格录入数据分行 ALT+ENTER 4.特殊的填充 5.格式刷的运用 第二讲：单元格格式 第三讲：查找、替换和定位 1.按值查找 2.按格式查找 3.开启单元格匹配 4.模糊查找 （1）通配符？ 如果只查找两个字符，须注意勾选“单元格匹配”
（2）通配符* 示例：查找张*（姓张名*），用张~*即可 5.用名称框定位区域 示例：A9000：B10000 6.选取区域后在名称框中命名后定位区域 7.运用定位工具将合并单元格拆分的单元格进行批量复制 思路：先把空白单元格定位，再在编辑栏中输入=D4（其中D4为第一个拆分单元格上面一个单元格），按CTRL+ENTER ？如何把拆分后的单元格再批量合并为一个单元格 步骤：（1）先排序；（2）分类汇总；（3）选取表头下一个单元格至最后一行单元格，定位空白单元格后再合并单元格；（4）删除分类汇总；（5）复制合并单元格的一列，选择性粘贴（选格式）到须合并的一列。 第四讲：排序和筛选 1.简单排序 点定排序条件的一个单元格，再点升序或降序排列 2.利用排序插入行，即达到隔行插入效果 3.每面打印表头 在页面设置中，选打印标题中的工作表的顶端标题行中设置 4.筛选后复制数据
用定位工具中的“可见单元格”，再复制粘贴 5.运用筛选中的文本筛选和数值筛选工具 6.高级筛选的条件区域设置 如上表：即设置为筛选条件为“所有一车间的数据或发生额大于3000的数据”； 3000的数据” 7.高级筛选中的条件设置须用公式的情形 （1）如须筛选并复制表头的，则条件设置中须有错误的表头； （2）如筛选后不复制表头的，则条件设置中的表头为空 第五讲：分类汇总和数据有效性 1.使用分类汇总前，要先排序 2.复制粘贴汇总统计结果 运用定位工具，选取可见单元格 3.数据有效性主要用于设置单元格录入数据的条件 ？设置C 列付款方式中仅能输入现金、转账、支票 步骤：（1）选取数据有效性中的序列；（2）来源中，录入现金、转帐、支票，并用英文的逗号（,）隔开 第六讲：数据透视表
常用的excel函数公式大全 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式：C2 =IFERROR(A2/B2,"") 说明：如果是错误值则显示为空，否则正常显示。
2、IF多条件判断返回值 公式：C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明：两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明：如果返回值大于0说明在另一个表中存在，0则不存在。
2、统计不重复的总人数 公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。 四、求和公式
1、隔列求和 公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明：如果标题行没有规则用第2个公式 2、单条件求和 公式：F2 =SUMIF(A:A,E2,C:C) 说明：SUMIF函数的基本用法
3、单条件模糊求和 公式：详见下图 说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。
4、多条件模糊求和 公式：C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明：在sumifs中可以使用通配符* 5、多表相同位置求和 公式：b2 =SUM(Sheet1:Sheet19!B2) 说明：在表中间删除或添加表后，公式结果会自动更新。 6、按日期和产品求和
必修：excel上机考试知识点 一、数据的计算 1、函数法（共四个函数） ：单击填和的单元格——插入——函数——SUM——修改参数范围：单击填平均值的单元格——插入——函数——AVERAGE——修改参数范围 ：单击填和的单元格——插入——函数——MAX——修改参数范围：单击填和的单元格——插入——函数——MIN——修改参数范围2、：单击填结果的单元格——输入公式例如：输入“=(B2-C2)*(E2-D2)” 二、：选择参与排序的所有数据——数据——排序——选择主要关 键字（例如按总分排序，关键字则选总分）——选择升序或降 序 三、：单击自动填充柄拖拽鼠标（即在选中的单元格右下角单击鼠 标拖拽） 四、：行标或列标上单击右键 五、右键点击——设置单元格格式（小数点精确几位，文本，百分比，字体对齐方式靠下，填充颜色合并居中，内边框和外边框等） 六、：选中数据区域——插入——图表（标题、分类标志、 行列选择、显示值、显示图例）上机主要考的图表有簇 状柱形图、三维饼图、折线图三种。
七、，填充颜色：插入——图片——自选图形——星星—— 在指定位置拖拽 八、：格式——条件格式 九、：在窗口左下脚双击Sheet1改为学生成绩。 十、选中数据区域——数据——筛选——自动筛选 十一、：视图——显示比例 第三章信息加工与表达 第四节数据信息的加工——计算和排序 一、数据的计算 （一）函数法 １、SUM( ) 定位置：单击要填写计算总和的单元格。 找函数：选择“插入”菜单中“函数”选项，在“插入函数”属性框中选择求和函数SUM，点击“确定”，如下图所示。或者直接单击工具栏中的求和函数按钮。 选范围：在“函数参数”属性框中观察求和数值的范围是否正确，如不正确，则手工修改下图中圈起的位置，将其改为正确的范围，或用鼠标直接在正确的数据区域上拖拽。最后执行“确定”按钮 2、AVERAGE( ) 定位置：单击要填写计算平均数的单元格。 找函数：选择“插入”菜单中“函数”选项，在“插入函数”属性框中选择求平均函数AVERAGE，点击“确定”，如下图所示。 选范围：在“函数参数”属性框(如下图所示)中观察求平均数值的范围是否正确，如不正确，则手工修改下图中圈起的位置，将其改为正确的范围，或用鼠标直接在正确的数据区域上拖拽。最后执行“确定”按钮。 3、MAX( ) 定位置：单击要填写计算最大值的单元格。 找函数：选择“插入”菜单中“函数”选项，在“插入函数”属性框中选择
Excel常用函数公式大全 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式： =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和； 2、平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数； 3、排名：=RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分：=MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分； 7、最低分：=MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分； 8、分数段人数统计： （1）=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格； （2）=COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格； （3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格； （4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；
1、利用身份证号码提取员工性别信息 我国新一代的18 位身份证号码有一个很明显的特征，身份证号的倒数第2 位是奇数，为男性，否则是女性。根据这一特征，利用MID 和TRUNC两个函数判断员工的性别，而不必逐个输入，这样既避免了输入的烦琐工作，又保证了数据的正确性 操作步骤： 在单元格区域E3:E19 中输入员工的身份证号码。 MID 返回文本字符串中从指定位置开始指定数目的字符，该数目由用户指定。格式：MID(text,start_num,num_chars)。参数：text（文本）代表要提取字符的文本字符串；start_num（开始数值）代表文本中要提取字符的位置，文本中第1 个字符的start_num 为1，以此类推；num_chars（字符个数）指定MID 从文本中返回字符的个数。
函数TRUNC 的功能是将数字的小数部分截去，返回整数。格式：TRUNC(number,num_digits)。参数：number（数值）需要截尾取整的数字。num_digits（阿拉伯数字）用于指定取整精度的数字，num_digits 的默认值为0。 2、利用身份证号码提取员工出生日期信息 利用身份证号码来提取员工的出生日期，既准确又节省时间。具体操作步骤如图
函数TEXT 功能是将数值转换为指定数字格式表示的文本。格式：TEXT(value,format_text)。参数：value（数值）指数值、计算结果为数字值的公式，或对包含数字值的单元格的引用；format_text（文本格式）为【单元格格式】对话框中【数字】选项卡上【分类】文本框中的文本形式 的数字格式。函数LEN 功能是返回文本字符串中的字符数。格式：LEN(text)。参数：text 表示要查找的文本，空格将作为字符进行计数。 3、计算员工年龄 企业中的职务变动和员工的年龄有密切的关系，员工年龄随着日期变化而变动，借助于函数YEAR 和TODAY 可以轻松输入。 选择单元格区域F3:F19，单击【开始】选项卡，在【数字】组中单击
----- 必修： excel 上机考试知识点 一、数据的计算 1、函数法（共四个函数） 求和函数： SUM( ) ：单击填和的单元格——插入——函数—— SUM
——修改参数范围 单击填平均值的单元格——插入——函数：)求平均函数： AVERAGE( —— AVERAGE ——修改参数范围
最大值函数： MAX( ) ：单击填和的单元格——插入——函数—— MAX ——修改参数范围 最小值函数： MIN( ) ：单击填和的单元格——插入——函数—— MIN ——修改参数范围 2、公式法：单击填结果的单元格——输入公式例如：输入 “=(B2-C2)*(E2-D2) ” 二、数据排序：选择参与排序的所有数据——数据——排序——选 择主要关键字（例如按总分排序，关键字则选总分）——选择
升序或降序 三、数据自动填充功能：单击自动填充柄拖拽鼠标（即在选中的单 元格右下角单击鼠标拖拽） 四、设置行宽列宽：行标或列标上单击右键 五、设置单元格格式：右键点击——设置单元格格式（小数 点精确几位，文本，百分比，字体对齐方式靠下，填充颜色
合并居中，内边框和外边框等）------ ----- 六、根据数据生成图表：选中数据区域——插入——图表 （标题、分类标志、行列选择、显示值、
显示图例）上 机主要考的图表有簇状柱形图、三维饼图、折线图三种。七、插入一个五角星，填充颜色：插入——图片——自选图 形——星星——在指定位置拖拽 八、条件格式：格式——条件格式 工作表名称：在窗口左下脚双击Sheet1 改为学生成绩。十、九、 筛选：选中数据区域——数据——筛
EXCEL学习笔记 1.固定录入,选定单元格之后按住ctrl在鼠标左键点击单元格。 2.关于引用拖动的简化，可以不须用鼠标放在右下角后拖动，只需 要将鼠标放在右下角呈可拖动状态时双击鼠标即可。 3.多个单元格录入同内容，先用ctrl选定要录入的单元格在最后点击 的一个单元格内输入内容后按ctrl+回车。 4.重复上一次设置（操作），选定区域后按F4。 5.重复上一单元格（只是内容），在英文状态下是ctrl+’（单引号， 双引号那个键）。 6.插入当下日期，ctrl+；。 7.分数的录入，一种方法是修改单元格格式，另一种是用带分数输 入如输1/2,就输0 (之间有空格)1/2。 8.粘贴（内容、格式、一次快速粘贴），复制后在开始菜单下调出剪 贴板（alt+E+B），然后选择区域再点击剪贴板内的内容就可以了。 粘贴可以是值可以是格式或者都有。另外，复制（后选定区域）+enter也可（只能复制一次）。 9.智能填充（内容、数据、格式、公式），在输入1回车输入2后鼠 标点击右下角标志拖拉就可以自动填充（内容、数据）。也可以ctrl+拖拉，一般自动加1，也可采用序列规定步长值和最终值不用去拖拉。其他的比如星期也一样。也可用拖拉之后点右下角的标志选择填充内容还是格式（注意边框的去除可用此法）等。还有公式的填充，在一个单元格(E1)内设置了比如D1+C1的公式，只要向下
拖动就可以自动填充公式。 10.调节录入的方向(即回车后移动方向),选择”文件”—“高级”的第一 个就是。 11.自动调节单元格的尺寸，若是列之间则双击最上如DE间分隔线。 另外还可以在行号列号处右击可设置行高和列宽。 12.合并单元格与拆分单元格是相反的，按钮一样。 13.在单元格内自动换行，在设置单元格格式内设置。 14.上标下标，水分子的输入，选定2后在设置单元格格式内定为下 标即可。 15.关于单元格斜线与文字输入，斜线在设置单元格内，斜线上下的 文字则是先把文字输进去再强行换行（alt+enter）然后用空格调整位置。 16.找出符合条件的单元格，选定区域后（那一列），在条件格式中设 置，让其用颜色区分。 17.查找和替换，ctrl+F。 18.行列的增加删除，选定区域（行列）右击—插入便可。 19.行列的隐藏与取消隐藏，选定行列然后右击操作即可。 20.工作簿之间的复制可以直接复制粘贴也可以采用ctrl+alt+鼠标放 在边缘线上托到要粘贴的工作簿。 21.工作簿的重命名、增加、删除、添加颜色。 22.工作簿在excel文件之间的移动。先打开你要移到的excel文件然 后点击原来的工作簿右键—移动或复制然后选择就可以了。注意,
十个办公常用的最基本的Excel函数 今天和大家分享的这些Excel函数都是最基本的，但应用面却非常广，学会基本Excel 函数，也可以让工作事半功倍。 1、SUM函数 SUM函数的作用是求和。 统计一个单元格区域： =sum(A1:A10) 统计多个单元格区域： =sum(A1:A10,C1:C10) 2、AVERAGE函数 Average 的作用是计算平均数。 可以这样： =AVERAGE(A1:A10)
也可以这样： =AVERAGE(A1:A10,D1:D10) 3、COUNT函数 COUNT函数计算含有数字的单元格的个数。 COUNT函数参数可以是单元格、单元格引用，或者数字。 COUNT函数会忽略非数字的值。 如果A1:A10是COUNT函数的参数，其中只有两个单元格含有数字，那么COUNT函数返回的值是2。 也可以使用单元格区域作为参数，如： =COUNT(A1:A10)
4、IF函数（重点掌握） IF函数的作用是判断一个条件，然后根据判断的结果返回指定值。 条件判断的结果必须返回一个或TRUE或FALSE的值，即“是”或是“不是”。 例如： 给出的条件是B2>C3，如果比较结果是TRUE，那么IF函数就返回第二个参数的值;如果是FALSE，则返回第三个参数的值。 IF函数的语法结构是： =IF(逻辑判断,为TRUE时的结果,为FALSE时的结果) 增加内容： 1．IF函数的语法结构 IF函数的语法结构：IF(条件,结果1,结果2)。 2．IF函数的功能 对满足条件的数据进行处理，条件满足则输出结果1，不满足则输出结果2。可以省略结果1或结果2，但不能同时省略。 3．条件表达式 把两个表达式用关系运算符(主要有=，<>，>，<，>=，<=等6个关系运算符)连接起来就构成条件表达式。 4．IF函数嵌套的执行过程 如果按等级来判断某个变量，IF函数的格式如下： IF(E2>=85,"优",IF(E2>=75,"良"，IF(E2>=60,"及格"，"不及格"))) 函数从左向右执行。首先计算E2>=85，如果该表达式成立，则显示“优”，如果不成立就继续计算E2>=75，如果该表达式成立，则显示“良”，否则继续计算E2>=60，如果该表达式成立，则显示“及格”，否则显示“不及格”。
EXCEL常用函数公式及举例 一、相关概念 （一）函数语法 由函数名+括号+参数组成 例：求和函数：SUM（A1,B2,…) 。参数与参数之间用逗号“,”隔开（二）运算符 1. 公式运算符：加（+）、减（-）、乘（*）、除（/）、百分号（%）、乘幂（＾） 2. 比较运算符：大与（>）、小于（<）、等于（=）、小于等于（<=）、大于等于（>=）、不等于（<>） 3. 引用运算符：区域运算符（：）、联合运算符（,） （三）单元格的相对引用与绝对引用 例：A1 $A1 锁定第A列 A$1 锁定第1行 $A$1 锁定第A列与第1行 二、常用函数 （一）数学函数 1. 求和=SUM（数值1,数值2,……） 2. 条件求和=SUMIF（查找的范围,条件(即对象),要求和的范围） 例：（1）=SUMIF(A1:A4,”>=200”,B1:B4) 函数意思：对第A1栏至A4栏中，大于等于200的数值对应的第B1列至B4列中数值求和 （2）=SUMIF(A1:A4,”<300”,C1:C4)
函数意思：对第A1栏至A4栏中，小于300的数值对应的第C1栏至C4栏中数值求和 3. 求个数=COUNT（数值1,数值2,……） 例：（1）=COUNT(A1:A4) 函数意思：第A1栏至A4栏求个数（2）=COUNT(A1:C4) 函数意思：第A1栏至C4栏求个数 4. 条件求个数=COUNTIF（范围,条件） 例：（1）=COUNTIF(A1:A4，”<>200”) 函数意思：第A1栏至A4栏中不等于200的栏求个数 （2）=COUNTIF(A1:C4,”>=1000”) 函数意思：第A1栏至C4栏中大于等1000的栏求个数 5. 求算术平均数=AVERAGE（数值1,数值2,……） 例：（1）=AVERAGE(A1,B2) （2）=AVERAGE(A1:A4) 6. 四舍五入函数=ROUND(数值,保留的小数位数) 7. 排位函数=RANK（数值,范围,序别）1-升序0-降序 例：（1）=RANK(A1,A1:A4,1) 函数意思：第A1栏在A1栏至A4栏中按升序排序，返回排名值。 （2）=RANK(A1,A1:A4,0) 函数意思：第A1栏在A1栏至A4栏中按降序排序，返回排名值。 8. 乘积函数=PRODUCT（数值1,数值2,……） 9. 取绝对值=ABS(数字) 10. 取整=INT(数字) （二）逻辑函数
计算机二级考试MS_Office应用Excel函数 =公式名称（参数1，参数2，。。。。。） =sum(计算范围) =average(计算范围) =sumifs(求和范围，条件范围1，符合条件1，条件范围2，符合条件2，。。。。。。) =vlookup(翻译对象，到哪里翻译，显示哪一种，精确匹配) =rank(对谁排名，在哪个范围里排名) =max(范围) =min(范围) =index(列范围，数字) =match(查询对象，范围，0) =mid(要截取的对象，从第几个开始，截取几个) =int(数字) =weekda y(日期，2) =if(谁符合什么条件，符合条件显示的内容，不符合条件显示的内容) =if(谁符合什么条件，符合条件显示的内容，if(谁符合什么条件，符合条件显示的内容，不符合条件显示的内容)) SUM函数 简单求和。 函数用法 SUM(number1,[number2],…) =SUM(A1:A5)是将单元格 A1 至 A5 中的所有数值相加； =SUM(A1,A3,A5)是将单元格 A1,A3,A5 中的数字相加。 SUMIFS函数 根据多个指定条件对若干单元格求和。 函数用法 SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 1） sum_range 是需要求和的实际单元格。包括数字或包含数字的名称、区域或单元格引用。忽略空白值和文本值。 2) criteria_range1为计算关联条件的第一个区域。 3) criteria1为条件1，条件的形式为数字、表达式、单元格引用或者文本，可用来定义将对criteria_range1参数中的哪些单元格求和。例如，条件可以表示为32、“>32”、B4、"苹果"、或"32"。 4）criteria_range2为用于条件2判断的单元格区域。 5) criteria2为条件2，条件的形式为数字、表达式、单元格引用或者文本，可用来定义将对criteria_range2参数中的哪些单元格求和。 4）和5）最多允许127个区域/条件对，即参数总数不超255个。 VLOOKUP函数 是Excel中的一个纵向查找函数，按列查找，最终返回该列所需查询列序所对应的值。
By8305樊斌老师 ERP（标准化业务） Excel（非标准化业务）：个性化事情流程标准化 基于会计解决问题的角度，工作能力。 数据是C公司上半年销售情况统计，本案例要求综合运用所学知识分别从产品销售情况和员工销售业绩两个维度对销售数据进行整理和评估分析。 1、销售数据整理与分析 （1）利用超级透视表的合并查询功能（或者是用pp中建模后再关联）进行关联，填充销售明细表 （2）制作透视表分析销售数据 2、销售业绩数据整理与分析 （1）对员工销售业绩汇总和排名 （2）利用countifs和排名函数填写按月统计表 （3）利用透视表对部门销售情况进行分析 （4）利用透视表特殊功能发奖金（备注：计算字段和计算项、色阶、以中间人为标杆、切片器、表格和透视表动态联动） （5）查询销售人员的情况（有效性设置与建模辅助工具的运用） 3、财务分析工具的运用 （1）运用模拟预测表进行销售预测分析 （2）运用规划求解销售数量 创新点： （1）将公司的销售数据从人力和物力（即产品）两个角度充分挖掘数据信息。 （2）穿插超级透视表，灵活运用函数功能 （3）加入各种细小的高级功能，比如查询系统、按钮、有效性设置等 （4）实现财务分析工具的综合运用，有一定难度 第二节课 电子报表产品 地址：两个案例。 2、折旧法： 直线法＝SLN(原值,残值,使用期限） 年数总和法＝SYD(原值,残值,使用期限,第几期） 双倍余额递减法＝VDB(原值,残值,使用期限,起始期,截止期,折旧因子,转直开关0) 3、复利终值系数表 三维地址高效编汇总表：shift 四维地址：跨表 第三节课（透视表） 对数据清单进行多维统计分析 财务会计向管理会计（非财务）转型：财务指标是滞后的。项目决策的话语权。 数据、工具运用、idea