EXCEL重点1——基本操作和函数.doc

需要掌握的内容大纲（操作在下面）一、 数据的输入1. 直接输入和使用编辑栏输入2. 自定义下拉列表输入3. 填充柄输入和自定义序列填充柄输入4. 条件格式5. 特殊内容的输入和控制（分数，负数，大小写，超链接等）6. 使用函数对数据进行舍入二、 函数与公式1. 函数和公式的基本用法2. 单元格的引用类型和基本方法3. excel中相关对象名称的创建和使用4. 重点函数的使用（SUM，AVERAGE，IF）5. 常用函数介绍（财务，文本，日期与时间，查找与引用，数据库控制，其他）三、 数组的使用1. 数组概述和使用数组常数2. 编辑数组3. 数组公式使用四、 数据管理和分析1. 数据列表2. 数据排序3. 筛选4. 分类汇总5. 数据透视表6. 数据导入与导出各重点内容操作方法一、 数据的输入1. 直接输入和编辑栏输入（略）2. 自定义下拉表输入：表示在单元格内做出一个 下拉菜单，使得我们可以直接选择而不用自己打字进去，可以免去很多重复的打字步骤。操作如下：a) 选中要输入内容的列b) 点击菜单栏数据有效性c) 在“设置”选项卡的“允许”处，选择“序列”d) 在下面的“来源处”输入各个选项，每个选项之间用英文逗号隔开（如蔬菜类,水果类,海鲜类）注意：“来源处”还有另一种输入选项的方法，就是用对象的“名称”，将在下文中详细讲述3. 自定义序列填充柄：按照自己定义的数据进行填充方法一：a) 在单元格中输入序列的内容，如“张三”，“李四”，“王五“，写在一列中b) 选中这些单元格c) 点击菜单栏工具选项，选择“自定义序列“选项卡d) 点击“导入“。在填充时候就能使用该序列方法二：在方法一的步骤c后，直接在“输入序列“下方输入序列内容，每打一个内容加个回车，打完后点击“添加”。4. 条件格式（与计算机一级一样，略）5. 特殊内容的输入和控制a) 分数：如要输入“5/9”，需要先输入“0”，再空格，再输入“5/9”。b) 负数：如要输入“-15”，可以输入“（15）”，再回车c) 文本类型的数字（可以保留数字最前的0）：如要输入“007”，先打英文单引号，再输入“007”d) 特殊字符（特殊的符号和不常用的字）：选中单元格点击菜单栏中插入符号自己选择即可e) 快速输入大写数字：选中单元格输入阿拉伯数字单元格格式数字选项卡特殊中文大写数字f) 取消系统自动生成超链接功能：操作如下1) 点击菜单栏工具自动更正选项键入时自动套用格式2) 取消“internet及网络路径替换为超链接”前的勾g) 同时取消很多个已经存在的超链接：操作如下1) 在任意单元格输入“1”，复制该单元格2) 按住ctrl键选中含有超链接的单元格3) 点右键选择性粘贴运算处选择“乘”确定6. 使用函数对数据进行舍入a) 舍入带最接近倍数：MROUND例：mround(168,5)，最后的结果为170，原因是在5的倍数中，170和168最接近。思考：mround(54,5)得出的结果是几？55b) 货币值舍入：如当算价格时候，算出的价格为43.54684元，就要舍入1) Ceiling：向上舍入到最接近的值2) Floor：向下舍入到最接近的值3) Round：四舍五入c) 根据位数直接去掉相关小数部分（没有舍入）：trunc例：trunc（75.6666，3）,最后的结果为75.666，表示保留小数3位d) 直接去掉全部小数（没有舍入）：int例：int(17.8)=17，负数比较特殊：int(-17.3)= -18e) 把一个数字舍入到n位有效数字1) 如果是不带小数的正数：rounddown(e1,3-len(e1),其中e1是个单元格，也可以直接打数字，3表示从左到右保留多少个有效数字。例：rounddown(88557,2-len(88557)，最后结果为880002) 如果是非整数或负数：round(e1,e2-1-int(log10(abs(e1),其中e1中是数字，e2中是需要保留的有效位数。例：如果e1中是2.545684，e2中为3，那么最终结果为2.55f) 时间的舍入1) 舍入到分钟：round(e1*1440)/1440例子：如果在e1中的时间为13：42：56，则最终结果为13：43：002) 舍入到小时：round(e1*24,0)/24例子：如果在e1中的时间为13：42：56，则最终结果为14：00：003) 舍入到最接近的15分钟（也可以是其他）的倍数时间：round(e1*24/0.25,0)*(0.25/24)，其中0.25表示1/4，因为15分钟是1/4小时。例子：如果在e1中是时间为15：35：12，则最终结果为15：45：00。（因为15：45是15分钟的倍数，而且在倍数中它离15：35：12最近）二、 函数与公式1. 基本输入方法，按等号后输入。略2. 认识基本运算符：+ -*/%=&:,空格。3. 单元格引用（在输入公式和函数时，不直接输入数据，而输入数据所在单元格的名字，这样的操作称为单元格引用，常见的引用方式有五种，在复制单元格或使用填充柄时，会产生不同结果。如果不复制的话，用什么方式引用区别不大）a) 相对引用：直接输入单元格名字，不加任何符号。在复制时，单元格名字会随粘贴的位置不同而改变。因此计算结果也会改变。b) 绝对引用：输入时在单元格的列标和行号前都加上$符号。当被复制时，加有$符号的单元格名字就不会发生变化。如我们在C1中输入“=$A$1+B1”，当我们把C1复制到其他单元格后，其中的$A$1永远保持不变，而B1就会根据情况发生变化。c) 混合引用：就是在列标或者行号前加上$符号。d) 三维引用：就是在输入时，引用到另一个表中的数据。写法为：表名字！单元格名字。例子：=sheet1!A1+sheet2!B1e) 循环引用：在输入公式或函数时，引用到自身单元格。如在A3中输入=A1+A2+A3。此时系统会弹出“循环引用”工具栏，要求定义循环引用，去除循环引用。如果用户本身就希望系统循环引用并计算，可以设置迭代计算，操作如下：i. 点击菜单栏工具选项重新计算ii. 在迭代计算上打钩，并设置迭代次数和误差4. 创建和使用名称通过为对象（表，单元格，常量，图表等）创建个名称，我们就能在公式中直接使用该对象。a) 定义名称：选中要定义的对象（如单元格）点击菜单栏插入名称定义输入名称选择数据范围b) 使用名称：在要输入公式或函数的时候直接输入名称即可。或者如果忘记名称了，可以点击菜单栏插入名称粘贴5. 重点函数a) Sum：求和，略b) Sumif：条件求和。Sumif（条件所在单元格，条件，数据所在单元格）系统将会判断条件所在单元格内的数据是否满足条件，满足了，则对数据所在单元格内的数据进行求和。c) If：判断并输出，略d) If的嵌套：如if（条件1，结果1，if（条件2，结果2，结果3）系统将先判断单元格内的数据是否满足条件1，如果满足，输出结果1，如果不满足，再去判断是否满足条件2，满足输出结果2，不满足输出结果3。例子：判断是否没货，货多，货少if(e1=0,”没货”,if(e110,”货多”,”货少”)6. 其他专用函数介绍a) 财务类：i. PMT：计算贷款按年、按月的偿还金额用法：PMT（rate，nper，pv，fv，type）rate：利率，以年为单位，如果要求月还款，除以12，求季度就除以4nper：贷款期限，单位为年，如果求月还款，乘以12pv：贷款数量，单位为元fv：最后一次想得到的现金余额，可以不写，不写为0type：还款时间，1为期初，0为期末，不写为0ii. IPMT：计算每个月的利息（每个月还款后，剩下的本金不同，利息也就慢慢变少）用法：ipmt（rate，per，nper，pv，fv）rate：利率per：想知道第第几期的利息nper：总期数，如分20年还，按月还，那么总期数就是240pv：贷款额fv：最后一次想得到的余额，默认0iii. Fv：计算投资的未来收益（条件是固定利率，等额分期付款），如果你是贷款的人呢，那么计算的就是你在最后总够会还给放贷人多少钱（本金+利息）。用法：fv（rate，nper，pmt，pv，type）rate：利率nper：总投资期限（如果是还款，就是还款期限）pmt：接下去还要每期投多少钱（如果是还款，就是每期还多少钱）pv：目前的本金（如果是还款，就是贷了多少钱）type：付款时间（期末或期初，0和1）iv. Pv：某项投资所需要的金额（去掉回报后的金额）用法：pv（rate，nper，pmt，fv，type）如预计每年投资15000，投10年，年回报15%，计算真正最后投入多少钱（去掉回报后），注意在写的时候pmt必须写为负数v. Sln：计算物品的折旧值（可以按日，月，年，默认为年）用法：sln（cost，salvage，life）cost：原来价值多少salvage：残值，就是旧货目前值多少，按月就*12，按日就*356life：用了多少时间b) 文本函数i. EXCAT：比较两个文本是否相同，相同得到“TRUE”，不同为“FALSE”，大小写和空格也会区分ii. CONCATENATE：把两个或多个字符文本或单元格内的数据连接，并显示在一个单元格内iii. SUBSTITUTE：替换文本字符串中的某个特定字符串用法：SUBSTITUTE（text,old_text,new_text,instance_num）text：原始内容或单元格old_text：其中要被替换的字符串new_text：替换为什么instance_num：如果old_text有多个，要替换其中哪个，不设置的话就全换iv. REPLACE：与上类似，可以控制要替换第几位文字用法：replace(old_text,start_num,num_chars,new_text)start_nu