Excel学习笔记

E X C E L学习笔记

1.固定录入,选定单元格之后按住ctrl在鼠标左键点击单元格。

2.关于引用拖动的简化，可以不须用鼠标放在右下角后拖动，只需要将鼠

标放在右下角呈可拖动状态时双击鼠标即可。

3.多个单元格录入同内容，先用ctrl选定要录入的单元格在最后点击的一

个单元格内输入内容后按ctrl+回车。

4.重复上一次设置（操作），选定区域后按F4。

5.重复上一单元格（只是内容），在英文状态下是ctrl+’（单引号，双引

号那个键）。

6.插入当下日期，ctrl+；。

7.分数的录入，一种方法是修改单元格格式，另一种是用带分数输入如输

1/2,就输0 (之间有空格)1/2。

8.粘贴（内容、格式、一次快速粘贴），复制后在开始菜单下调出剪贴板

（alt+E+B），然后选择区域再点击剪贴板内的内容就可以了。粘贴可以是值可以是格式或者都有。另外，复制（后选定区域）+enter也可（只能复制一次）。

9.智能填充（内容、数据、格式、公式），在输入1回车输入2后鼠标点

击右下角标志拖拉就可以自动填充（内容、数据）。也可以ctrl+拖拉，一般自动加1，也可采用序列规定步长值和最终值不用去拖拉。其他的比如星期也一样。也可用拖拉之后点右下角的标志选择填充内容还是格式（注意边框的去除可用此法）等。还有公式的填充，在一个单元格(E1)内设置了比如D1+C1的公式，只要向下拖动就可以自动填充公式。

10.调节录入的方向(即回车后移动方向),选择”文件”—“高级”的第一个就是。

11.自动调节单元格的尺寸，若是列之间则双击最上如DE间分隔线。另外

还可以在行号列号处右击可设置行高和列宽。

12.合并单元格与拆分单元格是相反的，按钮一样。

13.在单元格内自动换行，在设置单元格格式内设置。

14.上标下标，水分子的输入，选定2后在设置单元格格式内定为下标即可。

15.关于单元格斜线与文字输入，斜线在设置单元格内，斜线上下的文字则

是先把文字输进去再强行换行（alt+enter）然后用空格调整位置。

16.找出符合条件的单元格，选定区域后（那一列），在条件格式中设置，

让其用颜色区分。

17.查找和替换，ctrl+F。

18.行列的增加删除，选定区域（行列）右击—插入便可。

19.行列的隐藏与取消隐藏，选定行列然后右击操作即可。

20.工作簿之间的复制可以直接复制粘贴也可以采用ctrl+alt+鼠标放在边缘

线上托到要粘贴的工作簿。

21.工作簿的重命名、增加、删除、添加颜色。

22.工作簿在excel文件之间的移动。先打开你要移到的excel文件然后点

击原来的工作簿右键—移动或复制然后选择就可以了。注意,选中建立副本就是复制。

23.冻结窗格在“视图”里面。

24.设置密码在“审阅”里。

25.求和求平均值，除了可以输入公式外还可以采用“选定区域（要多出一

格，用于求和的值的输出）”点开始面板的“自动求和”然后有几个可选择。还可使用函数求值，如求和采用sum(c3:h3)

26.函数，sum、average（平均值）、count（计数函数）、max、min、product

（乘积函数）。

27.排序，选定区域(要包括关键字,比如排序语文成绩则要包括科目这一栏)

在开始选项下点排序—自定义排序—关键字然后根据需要去选择。Rand()函数产生随机数len()函数测字数打了“len（”之后点击所在列再打“）”

注意按行还是按列排序。

28.数据有效性，就是规定输入内容的范围.性别选择，在数据选项里找到数

据有效性—设置—序列—男,女。注意这两个字之间的逗号是英文状态下的逗号。

29.数据筛选。自动筛选：选定区域（不要包括合并单元格）--排序和筛选

—筛选--（在表格上点击）三角形标志—数字筛选（日期筛选、文本筛选）---然后自己添加条件。高级筛选：在数据选项卡里面，点高级筛选---选定列表区域与条件区域（如政治>90其中条件>90必须与原表格

一致即政治下面是分数此处的90也要在其下面）便可。

这是两者都要满足，

满足其一便可。

30.分类汇总：前提是要先排序，根据公司名称汇总则要根据公司名称排序

后再汇总。在数据选项卡下，就是可以根据日期把同一天的比如提货量求总和。

31.数据统计比如排序、分类汇总等选定区域时不要把表格标题（表格上

合并单元格那一栏）选定。你好

32.创建数据图表：选定区域—插入选项卡—选择图表类型。当需要在图表

中写入标题时可采用插入文本框的方法。图表框可调内图表也可调大小。可自行改变图表各种性状。

33.输入一串很长数据如输身份证时：在英文状态下先输入单引号再输数字

就自动转为文本格式而不是科学计数法。

34.输入日期、时间、分数：日期用- 或/ 。时间用：隔开。输入当前

时间ctrl+shift+；分数，前加空格。

35.T ab键，作用是往右跳，若事先选定了区域则按tab键跳到右边时之后

又跳到下一行的左边。

36.A lt键加小键盘的一些数字组合可输入平方等特殊符号。

ALT+小键盘153=?

ALT+小键盘169=?

ALT+小键盘174=?

ALT+小键盘177=

±

ALT+小键盘178=2

ALT+小键盘179=3

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后面就自动添加一个

“岁”字。要小数点则也要自己规定格式”

岁”00”小时”00”分钟”输入2035时就显示20小时35分钟

38.高级查找输入*代表任何多个字符，？代表任何一个字符。

常用函数

1.if函数：=if(F14>5,”优秀”，”不优秀”) 注意引号必须为英文状态

下的引号。If的嵌套调用=if(F14>E14,”中”，if(F14

平”)) 用函数时可用F9查看每一步计算情况（选定运行F9）。

2.数组：横向用逗号，纵向用分号，分号之间的是同一行的元素。一位数

组、二位数组、三位数组、三维数组。

3.运算内存数组计算时要ctrl+shift+enter。

4.数组运算时是行与列相加时是先行的每一个元素与列第一个元素分

别相加，然后是与列第二个元素分别相加依次类推。若是行与行或列与列则是一一对应进行计算。计算后外面还有如sum函数则是把每一个元素相加。

5.计数：sum（if（B4：B9>80，1））大于80的有多少个。Count（if（B4：

B9&C4:C9=”优优”，））逗号一定要有。Sum（if（B30：

B35>2000,B30:B35,0））意思是把B30到B35中大于2000的元素相加，其中在运算中是把不大于2000的元素置零。

6.引用：相对引用A3，绝对引用$A$3固定引用A3格的内容。混合引用A$3

或$A3，第一个是固定3，第二个是固定A。切换用F4。

7.引用的应用--九九乘法表：

=IF(B$1<=$A2,B$1&"X"&$A2&"="&B$1*$A2,"")注意连字符&的使用。

8.逻辑:or(或)、and(且)、not(非)。其值为两种false和true。

=And(9>0,4>3)其值为true。Not(2>0)为false。Or(9>0,4<3)为true。

9.True和false与1和0的转换：

就是将其进行数学运算然

后就以0和1的形式表示出来。第一个表示添两个负号若非0就等于1，其他的同理。

10.计数：sum(--(条件、数组))满足条件的都变为1然后用sum函数将

数组元素加起来。=SUM(--(A28:A33&B28:B33="良优

"))+SUM(--(A28:A33&B28:B33="优良"))意思是将优良或者良优的进行计数。

11.注意or函数后只产生一个元素，true和false或者0和1。

12.必要时用“+”号代替。如10的计数=SUM(--(A28:A33&B28:B33="

优良")+(--(A28:A33&B28:B33="良优")))

13.And可以用*来代替，or可以用+来代替。作用作用主要体现在数组

上，多个元素的情况，and与or不支持数组。1*1仍等于1，但对于有一个为真则为真，注意在用+代替or在进行数组运算时对应元素都不为0则+后就变为2，要用if将其还原为1。如下

=SUM(IF(({FALSE;FALSE;FALSE;TRUE;FALSE;TRUE})+({TRUE;FALSE;FA LSE;FALSE;FALSE;TRUE}),1))

14.If函数中的简写：if（表达式，成立，不成立），若其中的成立或

者不成立时的地方不填写东西但是两个逗号都有则表示不管表达式如何其值都为0，即逗号后面不写东西就表示为0。若后一个逗号不写则表示当条件不成立时其值为false。False不参与数学运算。注意if函数的嵌套调用if（表达式，值，if（表达式，值））

15.Is类判断函数：

16.文本（text）与数字（number）相乘产生错误值，为文本此时要将

其去掉可使用iserr（）与if函数的结合使用。

计算已定的水果的平均值。

IF(ISERR(B19:B23*C19:C23),"",B19:B23*C19:C23)

17.-A36:E44表示把A36:E44内的所有内容添上负号，若给文本添负号

就会出错。

18.Count与Sum，前者只对数字不管是正是负是0都会记数但不会对文

本计数。Sum只是将数字相加。

19.MIN、MAX函数:参数可以是数字或文本,min(number1、number2、……)

但是文本不能转换为数字或参数是错误时会出现错误。如果逻辑值与文本不能忽略计算则用mina、maxa。

20.上限下限：设参数为a,则设a的上限为100，则为min(100,a).意

思就是取的值不会超过100.同理max(50,a)表示下限，就是取的值不小于50.

21.求满足几个条件的情况的值：要求求

满足2011年6月的最大最小值，采用连字符&第一列与第二列连看是否是20116 然后会得出一个false与true ，true与后面的一列值相乘就是后面的值。多条件（且）的情况本质上就是连字符&与乘*、sum\max\min 还有引用$A50:$A180的综合应用或者把减法也使用上。

22.SUMPRODUCT（）：将两数组对应元素相乘再相加。要求维数要相同

否则出错，对非数值型元素作0处理。此处不像*，*是对true作1，对false作0处理。

23.Sumif（）：SUMIF（条件判断的区域，条件，满足条件的要相加的

值区域）。

（区域与区域）运算原则:第一个区域与第二个区域(sum_range)是对应关系即第一个区域的第一个元素对应第二个区域的第一个元素,一一对应下去.表示第一个区域中第N个元素满足条件时就取第二个区域的第N

个元素，若有多个满足时就全部相加。注意：如果条件中（criteria）为数组则运算是先判断第一个区域中满足条件中第一个元素的有哪些对应取第二个区域的元素相加然后再求满足条件中第二个元素的值依此类推，条件为数组则sumif计算出来的也是一个数组。

第三个参数其区域与第一个参数（必须为区域，不可使用连字符判断，即如果要满足几个条件就是“且”则用此函数没法完成）为同一区域时可以不写且其前面的逗号也不要写。第二个条件判断要用”条件”表示，双引号不能少，如”>100”。如果条件中要求满足两种或几种（或）的可能都可以的话可以采用数组的形式，意思就是“或”。例如求花生与梨子的总量sum(SUMIF(A43:A51,{"花生","梨子"},B43:B51))。注意：此处SUMIF求出的是一个数组即花生和梨子的数量{花生数量，梨子数量}所以后面要用sum求和。

24.通配符：*与？ *表示任意多个字，？表示任意一个字。

SUMIF(A16:A23,"张*",B16:B23)

25.关键点：关于引用拖动的问题，注意其中

开始定义张*右边的单元格=SUMIF(A16:A23,D17,B16:B23)之后往下拖动求其他单元格，由于此处引用中字母未变但是对应的数字因为向下拖

动而会改变。所以要=SUMIF(A$16:A$23,D17,B$16:B$23)固定数字不让其向下走（此处拖到*三*处选定的区域就是从刘万龙开始的然后直到张九如下面一格）。注意其中的变与不变问题。

26.数组之间运算问题：

要求连杆瓦的销量采用sumif（）第一个参数选择红色框选区域，第三个

参数选择黄色框选区域，若求金额则要从金额处开始框选，这样才能对应

上要求的量。首先是1处连杆瓦对应销量，当到10处后移到1处的销量但不是连杆瓦就跳过一直到五月的连杆瓦再继续对应求值。对于乘法也是一

样总是第一个框选的第一个元素对应于第二个框选的第一个元素如此下去。

27.计数类函数：count（求数字个数）、counta（求非空个数，空格也是

存在的会计数）、countblank（求空值个数）后两个是相反的。

（）会将0也计数，一定要注意这点。具体使用中可应用1去除得到的那

个逻辑值，如果逻辑值是true则值为1否则出错，而出错的count函数是不会计算在内的。

（区域，条件：可以是数字、表达式、文本）：条件一定要用双引号。计数时要满足比如：大于1500小于2000的可采用减法用计算出的大于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作为被除数则除了以后再相加得到的就是职务的个数，因为对于2就变成1/2 有两个就是1 对于5也是一样。

处理有空单元格的情况：

=SUM(IF(ISNUMBER(1/COUNTIF(A37:A45,A37:A45)),1/COUNTIF(A37:A45,A 37:A45)))先把为0 的去掉然后求和。或者先判断是否为空把空用空代替=SUM(IF(ISBLANK(A37:A45),"",1/COUNTIF(A37:A45,A37:A45)))

(区域,条件)前面是一个区域后面也可以是区域,如果都是区域如

与结果就是一个数组{3;4;3;5}.注意存放结果的不再是一个单元格而是如后面区域一样四个单元格即输入函数的时候是选择四个单元格再输入函数.完全重复的问题

可以使用此法先比较第一个然后第二个然后第三个等形成数组(第一行与

所有区域比较,比较出一个数组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:B$14>B6)*1)+1

31.关于表格中等级采用数字或者分数代替的问题：（还可采用查找的方法，具体用什么方法看情况）

计算评分结果是根据等级找到分数乘以对应的所占比例。要用分数代替等级。

=SUM(SUMIF(A$27:A$30,B$20:D$24,B$27:B$30)*(A$20:A$24=A20)*B$19:D $19)

=SUM({10,4,10;10,8,8;10,8,8;6,10,10;6,6,8}*{TRUE;FALSE;FALSE;FAL SE;FALSE}*{,,})

注意：每一个分号之前对应相乘。即true与10,4,10相乘，后面的都为0，然后再与后面所占比例逗号对应相乘。简而言之就是分号对分号逗号对逗号。

（）：

第一个参数表示要查找的对象此处为编号，第二个为区域此处为表格区域第8行开始，第三个参数为要查找的对象的相关的东西所在列此处为第三列性别，第四列为查找方式为0（也可不写0代替）表示精确查找，另外1表示近似查找。

注意表格中两种情况省些最后一个参数的情况。

注意：要查找的东西（前面图中表编号）必须是选定区域的第一列否则会出错。（查找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内的函数。

的模糊用法用于找相近的东西（注意：是我们要查的东西比在查找的区域中的要大。比如，查20，然后找到有19和21则取19,。在下表中查4600则取4500一栏，如果是4400则取1500一栏）。模糊查找一定要用升序（在哪查找哪就是升序）

36.采用查找vlookup自制税后工资计算器：

=J44-IF(J44>3500,(J44-3500)*VLOOKUP((J44-3500),G$44:I$50,2)-VLOO KUP((J44-3500),G$44:I$50,3),0)

37.列的对换：在查找中要查找的内容必须为第一列显示的为其之后的列。若不是可使用if对换。

=IF({1,0},E10:E18,D10:D18)

日期时间函数

1.每个日期都对应一个数字，但数字并不都对应一个时间。1代表日期中

的一天。数字与日期的转换是ctrl+shift+~与ctrl+shift+#

基本知识：

前面三个是从总日期中提取各个值，第四个是将各分的合并。Date(年，月，日)里面参数可用具体数字代替(直接用数字就行不用引号)。

eomonth(指定的日期，第一个参数可以直接用年月日输入但要用引号，如果有引用有输入则：eomonth(A19&”-2-1”)注意连字符与引号。

Eomonth(“2012-2-1”,0)向前向后推月正数表向后负数表向前)可在设置单元格格式内指定其格式为只显示最后天数（月最后那天）或者再使用day()函数然后格式设置为常规便可（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再将其转换为日期提取月份

就是季度。

5.判断平年闰年只需判断2月的天数即可。

Datevalue(A13) 其中A13（字符串形式日期）内容是'2008-9-8注意数字前面的单引号表示这是字符串，使用此函数可将其转化为真正意义上的日期（序列数字形式）2008-9-8。Edate（参数1 ，参数2）edate()内的参数与eomonth内的参数一样，第一个参数是日期第二个是向前或者向后推几个月。0表示就是本月。Weekday（参数1，参数2）第一个参数为日期，第二个参数为类型根据所在国家选取，美国取1，中国取2。

6.=DATEDIF("2001-1-3",B16,"y")算时间差（工龄）

7.时间函数

=TIME(B15,C15,D15) 如果要将一个时间比如11:30:00加上69分钟可以=("0"&":69")+A26。

采用连字符的形式将其转化为分钟再与11:30:00相加。

用时间time计算出来的是以天为单位的有时需要进行换算

数学函数

1.mod(被除数，除数)求余数（余数的正负号与除数相同）。

2.int(数)、trunc(数，取小数位数不写默认为0)trun()函数的后一个

参数可写可不写，逗号也可不写。Int是取整，向左（指的是坐标轴）

取距离本数最近的整数（小于或等于本数）。而trunc是直接取就行了要取几位小数就取几位小数不用考虑其移动问题只需在数字上直

接取就行了比如trunc值为1，trunc，2)值为。

3.round(四舍五入无论正负) roundup（无论正负向远离0点取值如取

两位小数就是） rounddown（向靠近0取值） trunc（要几位直接取不退不进）后面两个虽说法不一样但取值效果一样。此外int取值是一直向左取值意思就是一直取小的无论正负取出的值都要小于等于

原值。

4.floor（基数，倍数）ceiling（基数，倍数）取值是取倍数乘以一个

整数后小于等于（floor）或大于等于基数的那个数（倍数与整数相乘的那个数）其中倍数并非整数，但如果基数是正数则倍数必为正数否则出错。另外如果倍数是负数则取值floor（取大）与ceiling（取小）调换（之前floor是取小ceiling是取大）。

5.随机数：rand()返回值是0<=rand()<1。生成a到b的随机数使用公

式rand（）*（b-a）+a 如果要有b则可将式子中的b加（相对于b很小的数）便可出现b，然后int取整。

不过求区间随机数使用randbetween(最小值，最大值)更方便，但03版的excel可能出错，此时按

对于日期也可使用如=RANDBETWEEN("2000-1-1","2012-1-1")

6.乘积、乘幂：=PRODUCT(A5,B5，…)只有很多数相乘时才能显示其方便。POWER(number,power)第一个是数第二个参数是指数（幂）。当然指数也可用^代替。1 2 3 4 要把数字连起来可使用连字符&如果数字很多时可以使用指数=SUM(A27:D27*10^{3,2,1,0})