一、序文
为何突然做一个关于excel的博文呢,因为我是想做策划然后做制作人,不得不逼着自己去学下excel的操作,不然人家也不要我啊,不止要学excel 还要学vb matlab 统计学等等,oh my god!不瞎扯了,目前根据学习到内容进行一个总结,以后有新的学习点也会陆续更新进来。
二、Excel 基础知识
1. Excel的限制问题
excel也不是万能的,也会存在种种限制的情况,所以还是存在一些问题的。简单的列举一些内容:
1 、自定义格式的条件不能超过3个,但可以通过自定义单元格格式增加到6种颜色(边框等无法改)。 其他的限制有:不能输入常量数组、不能复制粘贴、不能更改条件顺序 |
2、 没有办法添加“自动套用格式”。 |
3 、函数的嵌套不能超过7层(但可以用“定义名称”的方法解除嵌套函数的限制(http://club.excelhome.net/dispbbs.asp?BoardID=1&ID=41113), 函数的参数个数最多 30 个(也有突破的办法http://club.excelhome.net/dispbbs.asp?BoardID=3&ID=37274) |
4、 名称的长度不能超过255个字符,在单元格的公式的字符限制是1024个字符。 |
5 、EXCEL 自动填充,字符型位数超过11位就不能自动填充。 |
6 、没有办法插入GIF动画图片。(但在程序开发中可以通过引用控件http://club.excelhome.net/dispbbs.asp?BoardID=2&replyID=33300&id=6531&skin=0或者 API时钟来实现http://club.excelhome.net/dispbbs.asp?BoardID=2&ID=15676) |
7 、对工作簿间的引用只能是绝对引用不能是相对引用。 |
8、 有效性中的引用必须是对单元格区域的引用,不能使用数组公式。 |
9 、单元格内的文字可以分别设置颜色字体,但是没有办法设置同一单元格格内两行文字的间距。 |
10、 在共享工作簿中,不能移动单元格,不能插入,不能排序,涉及移动数据的都不能操作。 |
11、 方案管理器中的方案最多有32个可变单元格,且在“可变单元格”中输入的公式将被转变为数值储存。 |
12、 找不到格式刷和合并单元格的快捷方式 |
13、 字体的最大字号是409磅,就是说最大打大约10cm×10cm大小的字。 |
14、 自动筛选列表中的行数最多1000行,但不代表没有显示的就无法筛选得到。 |
15 、"合并计算"功能每次只能保持一组合并数据. |
如果有什么奇葩的想法或者什么骚操作的话,可以自信约下度娘或者找一个谷哥,也可以找下Home大佬 。
2.Excel最强大的条件功能
1.条件求和
- 单条件求和
=SUMIF(D2:D17,"C1公司",E2:E17)
- 多条件求和
=SUM(IF(D2:D17="C2公司",IF(I2:I17="合格",E2:E17))),
2.条件计数
=COUNTIF(I2:I17,"合格"),
3.条件格式
选中区域格式,点击条件格式。
4.条件函数
=IF(H2>=85,"优良",IF(H2>0,"合格","未竣工"));
5.条件查找
Ctrl+F Ctrl+H
6.条件筛选
3.定义名称
名称的定义,对于excel来说是一个很强大的功能,因为它会给你带来非常实惠的妙处。
1.使用
Ctrl+F3
批量定义名称:Ctrl+Shift+F3
2.名称的定义
最好简单明了,不要和单元格的名称重复,类似于A1。还有很多无效的名称,系统会自动提示你。
引用位置:可以是工作表中的任意单元格,可以是公式,也可以是文本。在引用工作表单元格或者公式的时候,绝对引用和相对引用是有很大区别的。注意体会他们的区别 – 和在工作表中直接使用公式时的引用道理是一样的。
3.好处
好处 | 用法 |
---|---|
减少输入的工作量 | 如果你在一个文档中要输入很多相同的文本,建议使用名称。例如:定义DATA = “I LOVE YOU, EXCEL!”,你在任何单元格中输入“=DATA”,都会显示“I LOVE YOU, EXCEL!” |
在一个公式中出现多次相同的字段 | 例如公式=IF(ISERROR(IF(A1>B1,A1/B1,A1)),””, IF(A1>B1,A1/B1,A1)),这里你就可以将IF(A1>B1,A1/B1,A1)定义成名称“A_B”,你的公式便简化为=IF(ISERROR(A_B),””,A_B) |
超出某些公式的嵌套 | 例如IF函数的嵌套最多为七重,这时定义为多个名称就可以解决问题了。 |
字符数超过一个单元格允许的最大量 | 名称的引用位置中的字符最大允许量也是有限制的,你可以分割为两个或多个名称。 |
某些EXCEL函数只能在名称中使用 | 例如由公式计算结果的函数,在A1中输入’=1+2+3,然后定义名称 RESULT = EVALUATE(Sheet1!$A1),最后你在B1中写入=RESULT,B1就会显示6了。还有GET.CELL函数也只能在名称中使用,请参考相关资料。 |
图片的自动更新连接 | 例如你想要在一周内每天有不同的图片出现在你的文档中,具体做法是: 1. 找7张图片分别放在SHEET1 A1至A7单元格中,调整单元格和图片大小,使之恰好合适 2. 定义名称MYPIC = OFFSET(SHEET1!$A$1,WEEKDAY(TODAY(),1)-1,0,1,1) 3.控件工具箱 – 文字框,在编辑栏中将EMBED("Forms.TextBox.1","")改成MYPIC就大功告成了。 |
三、函数
-
时期函数
函数名 | 说明 | 用法 | 实例 |
---|---|---|---|
DATA | 返回代表特定日期的序列号 | DATA(year,month,day) year :1-4位数字 data : 最好的1-12的数字 如果大于12,year=yaer+data/12,data=data%12 day: 代表月份中的第几天,如果大于该月份的天数,则从指定月份的第一天开始往上累加。
|
=DATA(2008,1,35) 得到 2008/2/4 |
NOW | 返回当前日期和时间的序列号 | NOW() | =NOW() 得到 2019/3/27 13:03 |
TODAY | 返回今天日期的序列号 | TODAY() | =TODAY
2019/3/27 |
YEAR | 返回某日期对应的年份 | YEAR(searial_number) | B2=2019/3/27
=YEAR(B2)
2019 |
MONTH | 返回某日期对应的月份 | MONTH(searial_number) | =MONTH(B2)
3 |
DAY | 返回某日期对应的日期的天数用整数1-31表示 | DAY(searial_number) | =DAY(B2)
27 |
-
数学函数
函数 | 说明 | 用法 | 案列 |
---|---|---|---|
ABS | 返回数字的绝对值。绝对值没有符号 | ABS(number) | =ABS(-10)
10 |
INT | 将数字向下取整 | INT(number) | A2=2.25 A3=-2.225
=INT(A2) 2
=INT(A3) -3 |
FLOOR | 将参数Number沿着绝对值减小的方向向下摄入,使其等于最接近的significance的倍数。 |