excel实用技巧(学习心得-非常实用)

Excel实用技巧这是本人学习EXCEL函数时积累的学习心得和EXCEL的一些技巧(以函数为主)，非常实用，对学习EXCEL很有帮助。1、sum求和可以是不连续的多个区域,它可以用来求和,中间用英文逗号分隔开，也可以用来作多条件计数，如sum(A1:A10=60)*(A1:A1070)求60与70之间的个数。sum函数可以求数值、逻辑值（真为1,假为0），求和区域中的空格、文本不受影响，将不计算。Sum(A:A)表示引用整个A列2、F4绝对引用和相对引用切换.单元格内容的直接引用：如在需引用的单元格中写：＝B2表示在当前单元格中直接引用B2单元格的内容。跨表跨薄的直接引用：输完＝后，鼠标直接点击需引用的工作表中的单元格，甚至是另一工作簿中某个工作表中的单元格都可以。引用路径都会出来，非常方便且不易错，3、excel通过批注可以为数据添加解释说明文字；利用图表向导，可以快速建立不同类型的数据图表；想复制填充只要鼠标变成十字形，双击即可全部填充。4、在年级成绩总表中，统计学生班级名次：=sumproduct(($A$2:$A$1000=A2)*($H$2:%H$1000H2))+1。其中A2是其中一个条件一般为班级，H2是另一条件，一般为成绩。意思是先选出满足A2条件的人数，再同时满足H2的人数，再加1为该生的班级名次。5、下拉菜单可用：数据－有效性－序列－可以选择既定区域，也可输入文字、符写等，不过一定要英文逗号隔开。利用数据的有效性可以快速判断一些唯一数据是否有效，防止重复输入，如身份证号；也可以利用数据的有效性建立下拉菜单快速输入经常要输入的数据。利用数据的有效性可以实现自动切换中英文输入法，先选定好列，数据－有效性－输入法模式－模式－关闭（英文）或者有效数据－IME模式－打开。6、斜线表头设计办法：1、强制换行实现。2、上下标实现。3、插入文本框实现（改变文本框默认属性）。4、利用WORD制好表头后再复制到EXCEL粘贴。excel斜线单元格：先用工具画出斜线，再插入艺术字，设置好一个艺术字格式后，再按ctrl，同时鼠标拖动复制多个，修改文字后再拖到相应位置即可。7、excel不仅可以替换单元格中的字符，而且也可以替换公式中的字符。快速选取整列、整行的有效数据：选中整列、整行最左上单元格，按ctrl+ahift+下箭头+右箭头（注意必须是连续数据才能一次性选取）。Shift+方向键可以连续选取某方面上的单列或单行（可以没数据）8、可以利用excel锁定、隐藏和保护工作表的功能，把公式隐藏和锁定起来不让使用者查看和修改。也可以通过选择性粘贴，把结果转换为固定的数值。9、对于一些复杂的公式，我们可以利用“公式求值”功能，分段检查公式的返回结果，以查找出错的地方。10、空行快速删除：多空行时，利用自动筛选，选空白，删除即可。可以通过“选项”功能，为表格设置密码，防止别人打开。11、利用宏可以快速完成既定的枯燥的频繁的重复机械性工作。录制宏就是把一些操作像用录音机一样录下来中，到用时只要执行这个宏即可。但位置是绝对引用。12、ceiling为向上舍入取整的函数（收尾法），如ceiling(4.43,1),4.43为待舍入的数值，1为基数（意思是收尾法后加1）。INF函数为向下舍入最接近的整数，即去尾法求整数，concatenate函数能合并多个单元格内容，并可将若干文字串和某些单元格合并到一起。求优生线应该是向上舍入，即收尾法，用roundup函数.If函数表达式必须为判断比较语句（如＝。＞。＜等）。13、分数的输入：如果直接输入“1/5”，系统会将其变为“1月5日”，解决办法是：先输入“0”，然后输入空格，再输入分数“1/5”。14、序列“001”的输入如果直接输入“001”，系统会自动判断001为数据1，解决办法是：首先输入“'”（西文单引号），然后输入“001”。15、日期的输入如果要输入“4月5日”，直接输入“4/5”，再敲回车就行了。如果要输入当前日期，按一下“Ctrl+；”键。16、对多个工作表进行同时操作：在底部工作表上右键“选定全部工作表”也可用shift或ctrl加鼠标选中想要工作表，对选中的工作可同时进行页面设置、格式设置、查找替换等。17、将Excel信息以图片形式保存。将Excel中的信息以图片的形式放入Word文档或其他图像处理软件中，实现方法是：先在Excel工作表中选择需要的所有单元格，在按住“Shift”键的同时点击“编辑”菜单中的“复制图片”命令，在随后弹出的对话框中选中“如屏幕所示”复选框并[确定]，最后进入该图片的目标工作表或其他文档中点击“编辑”菜单中的“粘贴”命令就可以了18、=if（SUM（A1：A50，SUM（A1：A5），0）此式就利用了嵌套函数，意思是，当A1至A5的和大于0时，返回这个值，如果小于0，那么就返回0。19、round函数：按指定位数对数字进行四舍五入。如输入=round(3.158，2）则会出现数字3.16，即按两位小数进行四舍五入。rounddown：按指定位数舍去数字指定位数后面的小数。如输入=rounddown(3.158，2）则会出现数字3.15，将两位小数后的数字全部舍掉了。roundup：按指定位数向上舍入指定位数后面的小数。如输入=roundup(3.158，2）则会出现数字3.16，将两位小数后的数字舍上去，除非其后为零。20、DAVIRAGE函数返回数据库或数据清单中满足指定条件的列中数值的平均值..语法：DAVERAGE(database,field,criteria)参数：Database构成数据表或数据库的单元格区域。Field指定函数所使用的数据列（是写列标题），Criteria为一组包含给定条件的单元格区（可以是多条件，但必须把标题和条件的那个条件区域同时写进去）21、Vlookup函数可以从信息表中查找出单个信息制作工资条，单个员工信息，单个学生成绩等。用法：vlookup(查找值，查找区域，返回列数，精确查找false还是模糊查找true)..第一个参数为区域时，结果不成为内存数组。第二个参数可以整列引用，一般不影响速度，第二个参数可以是常量数组，也就是说可以不是引用区域，是手工输入的常量数组，也可以是引用区域后用F9查看后的结果，这样原来的引用区域可以删除而不影响查找效果，相当于部分代替了IF函数）。第四个参数如果为false可以写成0或省略，但英文逗号不能省。这个函数可以实现跨工作薄操作。Vlookup在查找区域中查不到查找值会返回错误提示，消错的办法如：if(countif(b:b,e3),vlookup(e3,b:c,2),””),计算B列满足E3的个数，如果大于0，则为true,返回第二个参数的值，否则返回空值，达到消错目的。Vlookup函数只返回第一个查找到的值，不理会不会返回下面的重复记录；查找值必须在查找区域的最左边存在，且完全一致；查找值与查找区域里的的相同值单元格格式都必须完全一致。当有重复值记录时，Lookup查找返回的是最后一个，Vlookup返回的是第一个。如果没重复记录用lookup查找快很多。Sumif也可以部分替代vlookup，条件是查找值必须是唯一的(否则就真是求和了)，且返回值必须为数值。查找速度也很快。Hlookup的用法与vlookup用法完全一样，包括所有特性、使用要求等都一样。唯独不同的是Hlookup是水平查找（行查找），而vlookup是垂直查找（列查找），Vlookup模糊查找有点不同，查找区域第一列必须升序排列，第四个参数不同，为true,或1或没有，逗号都不能有。使用不多，if可以部分代替。22、Match函数：=match(需查找的值，查找的单元格区域，查找方式）。查找方式有三种：为1时，查找小于等于需查找的值，用升序排列；为0时，查找等于需查找的值，任意排列；为-1时，查大于等于需查找的值，降序排列。23、=COLUMN()返回当前列号.=Row(单元格或单元格区域，或省略)。当省略时，表示返回函数所在单元格行号。当为区域时返回第一行行号。24、today()返回系统当前日期。Yeat()、month、day()分别返回某单元格日期中的年份、月份和日（参数必须填写）。Date返回代表特定日期的序列号，用法DATE（year,month,.day）（如果参数中的年、月、日超过范围自动向前面进位，称溢出）。Datedif(起始日期，结束日期，返回类型)，其中返回类型很多，如“y”,“m”,“d”,“md”，“ym”，“yd”等。Now()返回系统当前时间。Rand()返回按要求的随机数的函数。subtotal是返回数据清单和数据库的分类汇总。25、把数值强制变为文本：1、函数text().2、=要改变的单元格&。文本变成数值：加0或者乘1或前面加—都可以。26、单元格结果和公式的快速切换：Ctrl+~；关闭自动计算功能：工具，选项，重新计算里设置一下即可（对庞大的数据源计算非常有效），当数据修改好后需要计算时只要按下F9就可以全部重算。27、excel查看函数计算结果F9，28、＝MATCH($A1,$A$1:$A$9,)表示在$A$1:$A$9查找$A1，并返回其出现的位置。如果没有重复数据，则与ROW（）返回的相同。如果遇到重复数据则返回第一次出现该数据的位置值。与IF、NOW配合使用非常方便的可以找出重复数据。如：=IF(MATCH($A1,$A$1:$A$9,)=ROW(),,重复)（此为无标题行的情况，如有标题行则NOW（）减去行数。29、&是连接符，一般连接文本,单元格或单元格区域或者多条件连接等，如：A10&B10，A2:A10&B2:B10。或者A10&”|”&B10，A2:A10&”|”&B2:B10(多增加的“|”是为了避免两个关键字合并后产生歧义而进行的一种特殊处理。连接文本必须加英文引号。30、单条件求和常用函数：countif(条件区域，条件)。Sumif(条件区域，条件，求和区域).averageif(条件区域，条件，平均区域)。三个函数都可以使用通配符（英文状态哦）＊表示0－任意数，？只表示一个字符。在通配符前加~可使它变成普通符号，而失去通配符作用。sumif函数可以整列、整行引用而基本上不影响速度。但他们的1、3个区域参数一般不能套用函数（除间接引用函数，如offset,index,indirect等），只第2个条件函数可以套用。单条件求和一般避免合并单元格里求和，避免跨工作表求和31、多条件求和常用函数：countifs(条件区域1，条件1,条件区域2，条件2。。。)。Sumif(求和区域，条件区域1，条件1，条件区域2，条件2。。。).，averageifs(平均区域，条件区域1，条件1，条件区域2，条件2。。。)可以作为单条件求和函数使用，可以用比较运算符、可以整行整列引用，引用区域只能套用间接引用函数，可以用通配符。但与单条件函数相比也有一些不同：一是多条件求和所有的参数都不能省，二是引用区域可以错位，但引用区域必须绝对是一样大,否则出错。这三个函数是07版才有。03版主要采用sumproduct((条件区域1=条件1)*(条件区域2=条件2)…这是计数公式。如果求和再乘一个求和区域。Sum也可以采用相同的公式完成计数，求和等功能，不过必须是数组，即三键结束。Sumproduct不支持通配符（可以用其它函数配合才能使用），可以使用比较运算符，可以套用函数，慎用使用函数错位。慎用整行整列引用，原因1是03版不支持，2求和区域不能包括文本字符（如表头）或空单元格。3是这个函数整行整列引用严重影响速度。32、最好的多条件统计函数是数据库函数，全部是D字母打头函数，所有数据库函数使用方法完全一样能实现几乎所有的统计工作。参数必须带表头，且表头必须完全一致。当然它必须有辅助单元格，可以使用通配符，可以整行整列引用，可以用比较运算符，但要避免使用合并单元格，跨工作表和工作薄33、日期其实是整数，时间是小数。Ctrl+;可以