Excel学习笔记

EXCEL学习笔记

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”岁”00”小时”00”分钟”输入2035时就显示20小时35分钟

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

常用函数

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

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

if(F14

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;FAL SE;FALSE;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（求空值个数）后两个是相反的。

27.count（）会将0也计数，一定要注意这点。具体使用中可应用1去除得到的那个逻辑值，如果逻辑值是true则值为1否则出错，而出错的count函数是不会计算在内的。

28.COUNTIF（区域，条件：可以是数字、表达式、文本）：条件一定要用双引号。计数时要满足比如：大于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,A37:A45)))先把为0 的去掉然后求和。或者先判断是否为空把空用空代替=SUM(IF(ISBLANK(A37:A45),"",1/COUNTIF(A37:A45,A37:A45))) 29.countif(区域,条件)前面是一个区域后面也可以是区域,如果都

是区域如与结果就是一个数组{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;FALS E;FALSE;FALSE}*{0.5,0.3,0.2})

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

32.vlookup（）：

第一个参数表示要查找的对象此处为编号，第二个为区域此处为表格区域第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内的函数。

35.vlookup的模糊用法用于找相近的东西（注意：是我们要查的东

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

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

=J44-IF(J44>3500,(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.每个日期都对应一个数字，但数字并不都对应一个时间。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年一月一日然后会显示