EXCEL2007实战技巧(公式与函数)

1、EXCEL实用技巧、公式和函数、本教程主要针对中、高级Excel用户，内容主要涉及使用Excel公式和函数的基本方法和技巧。本教程使用office2007版本李宗耀2013年6月，内容，目录，1。公式和函数基础，1.1理解公式1.2公式1.3中的运算符公式1.4单元格引用1.5函数1.6名称，1.1理解公式，1.1.1公式的概念1.1.2公式的组成部分，表1-1公式的组成部分，1.1知道公式，1.1.3输入、编辑和删除公式，选择公式所在的单元格并按键。双击公式所在的单元格。选择公式所在的单元格，然后单击列标签上方的编辑栏。使用键和键清除单元格内容。1.1理解公式，1.1.4复制和填充公式，5种

2、复制和填充公式的方法方法1:拖放填充。方法2:双击填充手柄。方法3:快捷键填充。选择要填充的区域，然后按。方法4:选择性粘贴。方法5:同时输入多个单元格。示例：公式中的1.2运算符、1.2.1认知运算符、公式中的表1-2运算符、公式中的1.2运算符、1.2.2数据比较原则，注意：数字和数值是两个不同的概念！示例：表1-3 EXCEL中的数据类型、公式1.2中的运算符、表1-4中运算符的优先顺序、公式1.3中的常数、公式1.3.1中的常数参数以及公式中的五种常数，如=(5 8)*3。日期常量，例如：=datedif(“2012-1-1”，现在为()，“m”)。文本常量，例如：=“国际”和“复合”

3、。逻辑值常量，例如：=VLOOKUP(“张三丰”，A:B，2，FALSE)。误差值常数，例如：=COUNTIF(A:A，#DIV/0！)，注意：操作过程中逻辑值和文本数字会自动转换为数值！1.4单元格引用，1.4.1 A1引用样式和R1C1引用样式，A1引用样式默认情况下，Excel使用A1引用样式。也就是说，字母AXFD用于表示列标签，数字11048576用于表示行号。R1C1引用样式点击【办公】、【Excel选项】、【公式】选项卡，勾选“R1C1引用样式”，如下图：1.4单元格引用，1.4.2相对引用，绝对引用和混合引用，相对引用中下级单元格和引用单元格的相对位置不变。=A1绝对引用公式引

4、用的单元格的绝对位置不变。=A1混合引用所引用的单元格在行或列的一个方向上的绝对位置不变，而另一个方向上的位置会发生变化。例如，1.5函数，1.5.1函数的概念1.5.2函数的结构，注意：当一个函数被用作另一个函数的参数时，它就变成了函数的嵌套！1.5函数、1.5.3可选参数和必需参数、表1-5从常用函数中省略可选参数的情况、1.5函数、1.5.4常用函数的分类、内置函数根据函数和应用领域可分为11类，Excel函数根据不同来源可分为4类：内置函数、扩展函数、自定义函数、宏表函数、1 1 1.5函数、1.5.6公式使用中的常见问题、表1-6常见错误值及其含义、1.6为了便于公式的统一修改。替换

5、需要重复使用的公式以简化公式。存储常量数据而不是单元格区域。解决数据有效性和条件格式的问题，如常量数组、交叉引用和跨表引用。1.6名称，1.6.3名称命名限制，不能以数字开头。r、c、r和c不能用作名称。不能使用下划线、点和反斜杠以外的符号。字符不能超过255。字母不区分大小写。，1.6名称，1.6.4定义名称的方法，方法1:使用名称管理器定义名称，方法1:使用名称框快速创建名称，1.6名称，1.6.5具有已定义名称的对象，示例，文本处理。1合并和比较文本，2大小写转换，全宽转换，3字符和编码转换，4提取和搜索字符。2.1.1区分文本数字和数字值，默认情况下文本值左对齐，默认情况下数字值右对齐

6、，默认情况下逻辑值和错误值居中，默认情况下日期和时间右对齐，默认情况下数据对齐，文本形式的数字和数字值会导致查询错误，示例。1合并和比较文本，2.1.2合并文本，使用文本合并运算符，CONCATENATE函数和PHYOPIC函数，示例。1合并和比较文本，2.1.3比较文本值的大小， 例如，使用带有比较运算符“=”的EXACT函数，注意：当使用比较运算符比较文本时，它具有以下特征来区分全角字符和半角字符。 区分文本数字和数值，文本总是大于数值。字母不区分大小写。 2大小写转换，2.2.1大小写转换，LOWER函数：将所有字母转换成小写字母。UPPER函数：将所有字母转换成大写字母。正确的功能：将

7、所有单词转换成大写字母。例如， 2大小写，半宽转换，2.2.2全宽半宽字符转换(ASC，WIDECHAR)，全宽字符：也称为双音节字符，占据两个标准字符位置。半宽字符：也称为单音节字符，占据一个标准字符位置。所有的汉字都是全角字符，例如. 3字符和代码转换，2.3.1字符和代码转换函数(char，code)，ASC字符集B2312字符集BIG5字符集GB18030字符集Unicode字符集A(65)，A (97)，=char (64列(A1)=char (96列(A1右函数：从右端提取指定数量的字符)。中间函数：从中间提取指定数量的字符。例如，提取身份证信息。将中文和英文分开。 5替换和清除字

8、符，2.5.1常用替换字符功能，replace功能：用新字符串替换目标文本中已建立的字符串。替换(text，old _ text，new _ text，instance _ num)替换函数：根据起始位置和文本字符数，用指定的新字符串替换。替换(旧文本，起始字符数，字符数，新文本)，例如，1。如何将英语句子的第一个字母转换成大写？2.如何清除字符串中间的空格？数学计算，3.1余数函数，3.2数值选择函数，3.3舍入函数，3.4随机函数，3.1余数函数，使用模函数判断奇偶语法：模(数，除数)，返回被两个数除的余数。示例：MOD(69，7)，返回结果是7。示例，3.2数值权衡函数，常用权衡函数的总

9、结，3.3舍入示例，权衡函数应用示例，3.4 random函数，RAND函数和RANDBENT函数RAND函数：生成0到1之间的随机数；随机函数：根据给定的上限和下限生成随机整数。4.日期和时间计算，4.1月-日函数4.2理解日期函数4.3周相关函数4.4时间计算，4.1月-日函数，日期数据是一种特殊形式的数值，值1代表1900年1月1日。日期是整数值，时间是小数部分，数据0.5对应于时间序数12:00:00。常用的日期函数如下：4.1年月日函数，4.1.1使用TODAY和NOW快速生成当前日期和时间，使用和组合键快速输入当前日期和时间。使用该函数生成的日期和时间有什么相似之处和不同之处？4.

10、1.2使用DATE函数生成指定日期，=DATE(YEAR，MONTH，DAY)，例如，4.1.3使用年、月和日提取年、月和日，4.2知道DATEDIF函数，函数描述：计算两个日期之间的日、月和年语法：datedif (start _ date，end Unit)单位参数的含义如下：例如，4.3周相关函数， WEEKDAY函数：返回指定日期的周值WEEKNUM函数：返回指定日期属于整年的周，语法：WEEKDAY(serial_number，Return_type) return_type说明：数字1: 1到7对应星期日到星期六(1可以省略)数字2: 1到7代表星期一到星期日(中国习惯)数字3:

11、0到6代表星期一到星期日，例如4.4时间计算。 TIME函数：用于根据指定的数字生成特定的时间HOUR函数：用于提取时间序列值中的小时数MINUTE函数：用于提取时间序列值中的分钟数SECTION函数：用于提取时间序列值中的秒数。5.1基本查找功能5.2常用定位功能5.3行号和列号功能5.4特殊查找功能5.5了解偏移功能5.1基本查找功能vlOOKUP功能：搜索数据VLOOKUP(查找_值，表_数组，列_索引_号，范围_查找)hlookup功能：搜索满足搜索范围第一行条件的数据HLOOKUP(查找_值，表_数组，行_索引_号，查找)。例如，注意：如果参数rang_lookup为0或FALSE，

12、则意味着精确搜索。范围查找参数1或真表示模糊匹配查找。查找值与查找范围的数据类型匹配。5.2常用定位功能，匹配功能：确定搜索范围内搜索值的位置序号匹配(lookup _ value，lookup _ array，match _ type)，例如，判断数据是否重复输入，5.3行号和列号功能，行功能：返回单元格引用的行号列功能：返回单元格引用的列号，例如，1。如何设置背景颜色交错？2.设置数据查询和VLOOKUP功能。5.4特殊查找功能，查找功能：查找用户在查找范围内指定的查找值(查找值、查找向量、结果向量)，例如，5.5已知偏移量功能，偏移量功能：引用指定的引用。最后，通过给定的偏移量获得新的单

13、元格引用或单元格区域引用偏移量(引用、行、列、高度、宽度)。如何设置动态下拉菜单？例如，6。统计和求和，6.1基本计数函数，6.2条件统计类函数，6.3条件求和类函数，6.4极值应用函数，6.5排序应用，6.1基本计数函数，计数：计数数值，忽略错误值，逻辑值和文本值COUNTA:计算非空单元格数COUNTBLANK:计算数据表中的空单元格数，例如，6.2条件统计函数， Countif:根据指定条件(一)计算区域中数值的数量COUNTIF(范围，标准)COUNTIF:根据指定条件(倍数)计算区域中数值的数量，例如6.3条件求和函数，Sumif:根据指定条件(一)计算区域中数值的总和SUMIF(范围，标准，sum _ range) Sumifs:根据指定条件(一)计算区域中数值的总和Sumifs(计数(标准_ range1，标准1)，例如， 6.4极值应用函数，MAX:获取数据