Excel学习笔记Word格式.docx

1、Rand（）函数产生随机数 len（）函数测字数打了“len（”之后点击所在列再打“）” 注意按行还是按列排序。28. 数据有效性，就是规定输入内容的范围.性别选择，在数据选项里找到数据有效性设置序列男,女。注意这两个字之间的逗号是英文状态下的逗号。29. 数据筛选。自动筛选：选定区域（不要包括合并单元格）-排序和筛选筛选-（在表格上点击）三角形标志数字筛选（日期筛选、文本筛选）-然后自己添加条件。高级筛选：在数据选项卡里面，点高级筛选-选定列表区域与条件区域（如政治 90其中条件90必须与原表格一致即政治下面是分数 此处的90也要在其下面）便可。这是两者都要满足，满足其一便可。30. 分类汇

2、总：前提是要先排序，根据公司名称汇总则要根据公司名称排序后再汇总。在数据选项卡下，就是可以根据日期把同一天的比如提货量求总和。31. 数据统计 比如排序、分类汇总等选定区域时不要把表格标题（表格上合并单元格那一栏）选定。你好32. 创建数据图表：选定区域插入选项卡选择图表类型。当需要在图表中写入标题时可采用插入文本框的方法。图表框可调 内图表也可调大小。可自行改变图表各种性状。33. 输入一串很长数据如输身份证时：在英文状态下先输入单引号再输数字就自动转为文本格式而不是科学计数法。34. 输入日期、时间、分数：日期用 - 或 / 。时间用 ： 隔开。输入当前时间ctrl+shift+； 分数

3、，前加空格。35. Tab键，作用是往右跳，若事先选定了区域则按tab键跳到右边时之后又跳到下一行的左边。36. Alt键加小键盘的一些数字组合可输入平方等特殊符号。ALT+小键盘153=ALT+小键盘169=ALT+小键盘174=ALT+小键盘177=ALT+小键盘178=ALT+小键盘179=ALT+小键盘188=ALT+小键盘189=ALT+小键盘128=ALT 163= ALT 176= ALT 137=ALT 165= ALT 181= ALT 223= ALT 216= ALT 215= Alt+41420=37. 自定义单元格格式的输入：如每输入数字比如20后面就自动添加一个“岁

4、”字。要小数点则也要自己规定格式00.00”岁” 00”小时”00”分钟” 输入2035时就显示20小时35分钟38. 高级查找 输入*代表任何多个字符，？代表任何一个字符。常用函数1. if函数：=if（F145,”优秀”，”不优秀”） 注意引号必须为英文状态下的引号。If的嵌套调用=if（F14E14,”中”，if（F1480，1） 大于80的有多少个。Count（if（B4：B9&C4:C9=”优优”，）逗号一定要有。Sum（if（B30：B352000,B30:B35,0）意思是把B30到B35中大于2000的元素相加，其中在运算中是把不大于2000的元素置零。6. 引用：相对引用A3

5、，绝对引用$A$3固定引用A3格的内容。混合引用A$3或$A3，第一个是固定3，第二个是固定A。切换用F4。7. 引用的应用-九九乘法表：=IF（B$10,43）其值为true。Not（20）为false。Or（90,4100”。如果条件中要求满足两种或几种（或）的可能都可以的话可以采用数组的形式，意思就是“或”。例如求花生与梨子的总量sum（SUMIF（A43:A51,花生,梨子,B43:B51）。此处SUMIF求出的是一个数组即花生和梨子的数量花生数量，梨子数量所以后面要用sum求和。24.通配符：*与？ *表示任意多个字，？表示任意一个字。SUMIF（A16:A23,张*,B16:B23

6、）25.关键点：关于引用拖动的问题，注意其中开始定义张*右边的单元格=SUMIF（A16:A23,D17,B16:B23）之后往下拖动求其他单元格，由于此处引用中字母未变但是对应的数字因为向下拖动而会改变。所以要=SUMIF（A$16:A$23,D17,B$16:B$23）固定数字不让其向下走（此处拖到*三*处选定的区域就是从刘万龙开始的然后直到张九如下面一格）。注意其中的变与不变问题。26.数组之间运算问题：要求连杆瓦的销量采用sumif（）第一个参数选择红色框选区域，第三个参数选择黄色框选区域，若求金额则要从金额处开始框选，这样才能对应上要求的量。首先是1处连杆瓦对应销量，当到10处后移到

7、1处的销量但不是连杆瓦就跳过一直到五月的连杆瓦再继续对应求值。对于乘法也是一样总是第一个框选的第一个元素对应于第二个框选的第一个元素如此下去。27.计数类函数：count（求数字个数）、counta（求非空个数，空格也是存在的会计数）、countblank（求空值个数）后两个是相反的。27.count（）会将0也计数，一定要注意这点。具体使用中可应用1去除得到的那个逻辑值，如果逻辑值是true则值为1否则出错，而出错的count函数是不会计算在内的。28.COUNTIF（区域，条件：可以是数字、表达式、文本）：条件一定要用双引号。计数时要满足比如：大于1500小于2000的可采用减法用计算出的

8、大于1500的减去大于2000的。算出区域中不同种类的个数，即职务的个数：=countif（A24:A32,A24:A32）（注意要是其中有空单元格则会出现0，即空单元格不管出现几次都是用0表示看后面的绿色）就是区域与条件都选中同一区域，意思就是开始看后面的条件指向文员再到区域中看有几个文员发现有2个然后出现一个二，再到主管是一个再到业务员是5个，再到文员是2个，总的就是形成一个数组：2 1 5 2 5 5 1 5 5 （如果有空单元格则会出现像：2 0 3 0 4 2 3 0 4 4 3 这样的情况就不可像后面这么处理）就是数字为几就出现几次。如果用1作为被除数则除了以后再相加得到的就是职务

9、的个数，因为对于2就变成1/2 有两个就是1 对于5也是一样。处理有空单元格的情况：=SUM（IF（ISNUMBER（1/COUNTIF（A37:A45,A37:A45）,1/COUNTIF（A37:A45）先把为0 的去掉然后求和。或者先判断是否为空把空用空代替=SUM（IF（ISBLANK（A37:A45）,1/COUNTIF（A37:A45）29.countif（区域,条件）前面是一个区域后面也可以是区域,如果都是区域如与结果就是一个数组3;4;3;5.注意存放结果的不再是一个单元格而是如后面区域一样四个单元格即输入函数的时候是选择四个单元格再输入函数.完全重复的问题可以使用此法先比较第

10、一个然后第二个然后第三个等形成数组（第一行与所有区域比较,比较出一个数组2,4,3,1然后第二行第三行等）.如果全部都大于1则有重复否则没有.30.关于中式排名:利用种类的个数的方法. =SUM（B$6:B$14=B6）*1/COUNTIF（B$6:B$14,B$6:B$14）美式排名: =SUM（B$6:B6）*1）+131.关于表格中等级采用数字或者分数代替的问题：（还可采用查找的方法，具体用什么方法看情况）计算评分结果是根据等级找到分数乘以对应的所占比例。要用分数代替等级。=SUM（SUMIF（A$27:A$30,B$20:D$24,B$27:B$30）*（A$20:A$24=A20）*

11、B$19:D$19）=SUM（10,4,10;10,8,8;6,10,10;6,6,8*TRUE;FALSE*0.5,0.3,0.2）每一个分号之前对应相乘。即true与10,4,10相乘，后面的都为0，然后再与后面所占比例逗号对应相乘。简而言之就是分号对分号逗号对逗号。32.vlookup（）：第一个参数表示要查找的对象此处为编号，第二个为区域此处为表格区域第8行开始，第三个参数为要查找的对象的相关的东西所在列此处为第三列性别，第四列为查找方式为0（也可不写0代替）表示精确查找，另外1表示近似查找。注意表格中两种情况省些最后一个参数的情况。要查找的东西（前面图中表编号）必须是选定区域的第一列

12、否则会出错。（查找NED005，选定的区域也必须是从编号开始的。如果查找陈青则选区域时从名字开始就行。）33.关于表格中因一个变量引发其他发生变化的情况：如下图图中变量是p处41行，对于姓名处O列=IF（SUM（G$42:G$50=$P$41）*1）0,VLOOKUP（$P$41,$G$42:$M$50,2,0）,）红色$P$41是不变化的，然后2处是根据需要查询的列定的。如年龄就是5。其他一样推。抓住变量，变量在因变量中体现就影响因变量的值的变化。34.以工种或类别为区别依据表格填入数据（数据只与工种或类别有关）。采用精确查找的方法。如fx内的函数。35.vlookup的模糊用法用于找相近的

13、东西（注意：是我们要查的东西比在查找的区域中的要大。比如，查20，然后找到有19和21则取19,。在下表中查4600则取4500一栏，如果是4400则取1500一栏）。模糊查找一定要用升序（在哪查找哪就是升序）36.采用查找vlookup自制税后工资计算器：=J44-IF（J443500,（J44-3500）*VLOOKUP（J44-3500）,G$44:I$50,2）-VLOOKUP（J44-3500）,G$44:I$50,3）,0）37.列的对换：在查找中要查找的内容必须为第一列显示的为其之后的列。若不是可使用if对换。=IF（1,0,E10:E18,D10:D18）日期时间函数1. 每个

14、日期都对应一个数字，但数字并不都对应一个时间。1代表日期中的一天。数字与日期的转换是ctrl+shift+与ctrl+shift+#基本知识：前面三个是从总日期中提取各个值，第四个是将各分的合并。Date（年，月，日）里面参数可用具体数字代替（直接用数字就行不用引号）。 eomonth（指定的日期，第一个参数可以直接用年月日输入但要用引号，如果有引用有输入则：eomonth（A19&”-2-1”）注意连字符与引号。Eomonth（“2012-2-1”,0）向前向后推月正数表向后负数表向前）可在设置单元格格式内指定其格式为只显示最后天数（月最后那天）或者再使用day（）函数然后格式设置为常规便可

15、（day取出的是“日”但可能会以日期显示即1代表1900年一月一号，此时可用ctrl+shift+转换即可。）。2. 日期相减得到的是日（天）数但有时会自动转为数字对应日期的形式一定要特别注意。1代表1900年一月一日然后会显示1900-1-1或者只显示1。1940/6/19代表14781但有时会显示19.3. 注意：day（）取出年月日中的“日”，此数常“数字-日期”转换，用ctrl+shift+与ctrl+shift+#切换。Eomonth（ , ）取出所求月份最后一天的日期“年-月-日”可用设置单元格格式或用day（）函数转换。4. 求出所在日期所处季度：将月份提取乘10再将其转换为日期

16、提取月份就是季度。5. 判断平年闰年只需判断2月的天数即可。6. Datevalue（A13） 其中A13（字符串形式日期）内容是2008-9-8注意数字前面的单引号表示这是字符串，使用此函数可将其转化为真正意义上的日期（序列数字形式）2008-9-8。Edate（ 参数1 ， 参数2）edate（）内的参数与eomonth内的参数一样，第一个参数是日期第二个是向前或者向后推几个月。0表示就是本月。Weekday（参数1，参数2）第一个参数为日期，第二个参数为类型根据所在国家选取，美国取1，中国取2。7. =DATEDIF（2001-1-3,B16,y）算时间差（工龄）8. 时间函数=TIME

17、（B15,C15,D15）如果要将一个时间比如11:30:00加上69分钟可以=（0:69）+A26。采用连字符的形式将其转化为分钟再与11:00相加。用时间time计算出来的是以天为单位的有时需要进行换算数学函数1. mod（被除数，除数）求余数（余数的正负号与除数相同）。2. int（数）、trunc（数，取小数位数不写默认为0）trun（）函数的后一个参数可写可不写，逗号也可不写。Int是取整，向左（指的是坐标轴）取距离本数最近的整数（小于或等于本数）。而trunc是直接取就行了要取几位小数就取几位小数不用考虑其移动问题只需在数字上直接取就行了比如trunc（-0.9999）值为1，tr

18、unc（-0.9999，2）值为-0.99。3. round（四舍五入无论正负） roundup（无论正负向远离0点取值如0.981取两位小数就是0.99） rounddown（向靠近0取值） trunc（要几位直接取不退不进）后面两个虽说法不一样但取值效果一样。此外int取值是一直向左取值意思就是一直取小的无论正负取出的值都要小于等于原值。4. floor（基数，倍数）ceiling（基数，倍数）取值是取倍数乘以一个整数后小于等于（floor）或大于等于基数的那个数（倍数与整数相乘的那个数）其中倍数并非整数，但如果基数是正数则倍数必为正数否则出错。另外如果倍数是负数则取值floor（取大）与

19、ceiling（取小）调换（之前floor是取小ceiling是取大）。5. 随机数：rand（）返回值是0=rand（）1。生成a到b的随机数使用公式rand（）*（b-a）+a 如果要有b则可将式子中的b加0.1（相对于b很小的数）便可出现b，然后int取整。 不过求区间随机数使用randbetween（最小值，最大值）更方便，但03版的excel可能出错，此时按对于日期也可使用如=RANDBETWEEN（2000-1-12012-1-16.乘积、乘幂：=PRODUCT（A5,B5，）只有很多数相乘时才能显示其方便。POWER（number,power）第一个是数第二个参数是指数（幂）。当然指数也可用 代替。1 2 3 4 要把数字连起来可使用连字符&如果数字很多时可以使用指数=SUM（A27:D27*103,2,1,0）