关于Excel的应用集以及快捷操作运用（迭代更新）

 

一、序文

    为何突然做一个关于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就大功告成了。

 三、函数

1. 时期函数

函数名	说明	用法	实例
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

1. 数学函数

函数	说明	用法	案列
ABS	返回数字的绝对值。绝对值没有符号	ABS(number)	=ABS(-10)   10
INT	将数字向下取整	INT(number)	A2=2.25 A3=-2.225   =INT(A2) 2   =INT(A3) -3
FLOOR	将参数Number沿着绝对值减小的方向向下摄入，使其等于最接近的significance的倍数。