高效办公之excel基础篇.docx
《高效办公之excel基础篇.docx》由会员分享，可在线阅读，更多相关《高效办公之excel基础篇.docx（27页珍藏版）》请在冰豆网上搜索。
高效办公之excel基础篇
高效办公之officeexcel2003基础篇
一、数据的输入2
1负数的输入2
2日期的输入2
3时间的输入2
4文本默认显示规律2
5有效性数据输入3
6切换快捷键5
7复制填充有规律数据5
9序列填充6
10一键输入相同数据6
11自定义填充6
12利用记录单输入数据7
二、数据的格式化8
13格式单元格（略）8
14自定义样式8
15合并样式8
16自定义格式9
17Excel格式设置的组成部分9
18文本和空格。
10
18.1显示文本和数字10
18.2包括文本和数字10
18.3添加空格10
18.4重复字符10
19小数点的位置和有效数字10
20特殊符号代表的含义11
21千位分隔符11
22颜色11
23使用Alt+ascii码输入特殊字符12
24调整计算机默认格式15
25百分比格式控制符15
26真分数、科学计数法格式控制符16
27日期时间格式控制符16
三、排序、筛选、分类汇总17
28单个关键字的排序17
29多个关键字排序18
30自定义序列18
31自动筛选19
32取消自动筛选19
33自定义筛选19
34高级筛选20
35分类汇总20
36删除分类汇总21
四、数据透视表21
37数据透视表（数据-数据透视表）略21
五、公式与函数21
38输入公式21
39绝对引用、相对引用21
40单元格的引用方法22
41输入函数22
42按步计算嵌套公式22
43常见的错误23
六、练习题23
一、数据的输入
1负数的输入
比如负数-123.456,可以输入-123.456，也可输入（123.456）。
那么以括号开头的字符串如何输入呢？
如字符串“（123.456）”
前面加单引号再输入。
2日期的输入
年月日用-或/隔开即可。
如：
2010-4-23、2010/4/23。
3时间的输入
二十四小时制：
18:
56即可。
十二小时制：
时间+空格+上午（A或AM）下午（P或PM），大小写均可。
快速输入当前日期：
ctrl+；。
快速输入当前时间：
ctrl+：
（等价于ctrl+shift+；）
4文本默认显示规律
右边没有数据，超出部分顺延显示，可打印；右边有数据，超出部分不显示，打印不出。
因此，为避免excel数据打印不全，建议小表格复制到Word中打印。
5有效性数据输入
Excel会自动限制数据的有效性，默认为任意值。
自定义数据有效性举例，数据-有效性，如下，
允许选择-序列，来源输入或者选择：
领导,教师,学生（注意：
逗号用半角英文的逗号哈）。
切换到-输入信息，如下输入
切换到-“出错警告”
确定即可。
其他数据有效性自己尝试哈。
如何把数据有效性列表放在另外一张工作表？
在sheet2这个工作表中：
[插入]>[名称]〉[定义]，引用位置：
=Sheet2!
$A$1:
$A$3；名字：
df
选中sheet1的A1，数据--有效性，允许：
序列，来源：
=df即可
6切换快捷键
切换项目
正序（先后、左右顺序）
逆序（后先、右左顺序）
窗口
alt+tab
alt+tab+shift
选项卡
ctrl+tab
ctrl+tab+shift
7复制填充有规律数据
这个大家都知道，我只演示一下哈。
演示内容，有人生日为阳历闰年闰月，公历闰年大家都会计算，每隔4年或者8年就会有一个闰年，自动填充。
年
第几个生日
1988
1
1992
2
8计算题，历法误差15天就认为会失效，我国采用的公历什么时候失效？
地球公转周期
365.2422
平年365天
365
每年会少过的天数
0.2422
四年平年少过的天数
0.9688
4年闰一天，4年多过的天数
0.0312
100年多过的天数
0.78
100年少闰1天，又少过的天数
0.22
400年少过的天数
0.88
400年闰1天，多过的天数
0.12
2010年多过的天数
0.603
误差15天，历法将会失效
50000
5万年后，公历不再准确！
格里历
9序列填充
输入初始值，选中，编辑-填充-序列，
确定完成，其他功能自己尝试。
10一键输入相同数据
选中区域，输入内容，ctrl+enter完成。
支持多张工作表，不同的工作表选中时记得按ctrl键。
11自定义填充
工具-选项，自定义序列选项卡，
确定即可。
12利用记录单输入数据
学号
姓名
性别
计算机
数学
语文
英语
20061577
马标行
男
78
45
78
12
选中区域，数据-记录单，
可以避免由于行列太多而搞混了，还可以加入数据有效性。
二、数据的格式化
13格式单元格（略）
14自定义样式
格式-样式，样式名中输入自定义名称后点添加，然后修改
应用自定义的样式方法，选中区域后，格式-样式，选择样式名，确定即可。
15合并样式
新建excel文档，格式-样式，点击合并，
确定后就把自定义样式复制过来了，呵呵
16自定义格式
00,格式位数不足的前面自动补充0的意思。
下面详细讲述一下excel自定义格式的控制符。
17Excel格式设置的组成部分
●正数部分的格式定义
●负数部分的格式定义
●0的格式定义
●文本的格式定义
格式代码中可以指定4个节，用分号隔开，分别表示正数、负数、零、文本四种格式的设置。
如果只指定一个节，表示该节应用于四个部分；如果指定两个节，第一节表示正数，第二节表示负数；分号之间为空，表示默认设置。
举例如下：
18文本和空格。
18.1显示文本和数字
同时显示的文本和数字
引用方法
举例
显示
多个字符
用双引号引起
0.00"剩余";-0.00"短缺"
123.45剩余
单个字符
用反斜杠引起
0.00\L;-0.00\S
-1234.46S
特殊字符
直接使用
￥0.00;-￥0.00
-¥123.46
特殊字符包括：
$
-
+
/
（
）
:
!
^
&
'（左撇号）
'（右撇号）
~
{
}
=
<
>
空格
￥
18.2包括文本和数字
在需要包括文本和数字的地方添加，并以@符号开头。
例如：
举例
输入
结果
@好
abcde
abcde好
18.3添加空格
在需要添加空格的地方写下滑线。
例如：
举例
输入
结果
0000_00000
12345678
12345678
18.4重复字符
使用星号之后的字符重复显示，知道填满整个单元格，例如：
举例
输入
结果
0.0*!
123.456
123.5!
!
!
!
!
!
19小数点的位置和有效数字
小数点左侧的位数为A位，实际为X位，如果A>=X，多的位数可以用0补齐，也可以采取其他的无用字符填充；如果A小数点右侧的位数设定为B位，实际有Y位，如果B>=Y，多的部分可以
举例
输入
结果
000.000
12.456
012.456
000.000
1234.456
1234.456
000.000
123.45
123.450
000.000
123.4567
123.457
20特殊符号代表的含义
控制符
代表意义
#
只显示有意义的数字，不显示无意义的0
0
表示数字的位数，当数字的位数小于定义的位数时候会显示无意义的0
?
为小数点两边无意义的0添加空格，目的是方便小数点对齐；还可以对不等长数字的分数使用？
举例：
控制符
输入
显示
#.000
0.1234
.123
?
.?
?
?
1.1
1.1
综合例子：
控制符
输入
显示
说明
##.###
123.4568
123.457
最多3位小数
#.0000
0.12
.1200
4位小数
0.#
.123
0.1
最多1位小数
#.00#
66
66.00
2~3位小数
#.00#
0.12345
.123
2~3位小数
0.?
?
?
0.1
0.1
3位小数点对齐
#?
?
?
/?
?
?
8.2
41/5
3位分母以分数线对齐
21千位分隔符
用逗号表示千位分隔符，还可以以千位单位缩小。
例如：
控制符
输入
显示
说明
#,###
1234567.123
1,234,567
显示千位分隔符
#,
123456.123
123
缩小1K
0.0,,
123456789
123.5
缩小1M，并控制格式
22颜色
颜色控制符必须放在最前，用中括号括起来
例如：
小于60的成绩用红色显示，大于60分的成绩用蓝色显示，最多1位小数，小数点对齐。
控制格式符：
[红色][<60]0.?
;[蓝色][>=60]0.?
23使用Alt+ascii码输入特殊字符
下表列出了字符集中的0-127。
代码
字符
代码
字符
代码
字符
代码
字符
0
32
[空格]
64
@
96
`
1
33
!
65
A
97
a
2
34
"
66
B
98
b
3
35
#
67
C
99
c
4
36
$
68
D
100
d
5
37
%
69
E
101
e
6
38
&
70
F
102
f
7
39
'
71
G
103
g
8
**
40
（
72
H
104
h
9
**
41
）
73
I
105
i
10
**
42
*
74
J
106
j
11
43
+
75
K
107
k
12
44
76
L
108
l
13
**
45
-
77
M
109
m
14
46
.
78
N
110
n
15
?
47
/
79
O
111
o
16
?
48
0
80
P
112
p
17
?
49
1
81
Q
113
q
18
?
50
2
82
R
114
r
19
_
51
3
83
S
115
s
20
_
52
4
84
T
116
t
21
_
53
5
85
U
117
u
22
?
54
6
86
V
118
v
23
?
55
7
87
W
119
w
24
?
56
8
88
X
120
x
25
?
57
9
89
Y
121
y
26
?
58
:
90
Z
122
z
27
_
59
;
91
[
123
{
28
?
60
<
92
\
124
|
29
?
61
=
93
]
125
}
30
-
62
>
94
^
126
~
31
63
?
95
_
127
?
下表列出了字符集中的128-255。
代码
字符
代码
字符
代码
字符
代码
字符
128
€
160
[空格]
192
À
224
à
129
?
161
¡
193
Á
225
á
130
‚
162
¢
194
Â
226
â
131
ƒ
163
£
195
Ã
227
ã
132
„
164
¤
196
Ä
228
ä
133
…
165
¥
197
Å
229
å
134
†
166
¦
198
Æ
230
æ
135
‡
167
§
199
Ç
231
ç
136
ˆ
168
¨
200
È
231
ç
137
‰
169
©
201
É
232
è
138
Š
170
ª
202
Ê
233
é
139
‹
171
«
203
Ë
234
ê
140
Œ
172
¬
204
Ì
235
ë
141
?
173
205
Í
236
ì
142
Ž
174
®
206
Î
237
í
143
?
175
¯
207
Ï
238
î
144
?
176
°
208
Ð
239
ï
145
‘
177
±
209
Ñ
240
ð
146
’
178
²
210
Ò
241
ñ
147
“
179
³
211
Ó
242
ò
148
”
180
´
212
Ô
243
ó
149
•
181
µ
213
Õ
244
ô
150
–
182
¶
214
Ö
245
õ
151
—
183
·
215
×
246
ö
152
˜
184
¸
216
Ø
247
÷
153
™
185
¹
217
Ù
248
ø
154
š
186
º
218
Ú
249
ù
155
›
187
»
219
Û
250
ú
156
œ
188
¼
220
Ü
251
û
157
?
189
½
221
Ý
252
ü
158
ž
190
¾
222
Þ
253
ý
159
Ÿ
191
¿
223
ß
254
þ
注释：
**数值8、9、10和13可以分别转换为退格符、制表符、换行符和回车符。
这些字符都没有图形表示，但是对于不同的应用程序，这些字符可能会影响文本的显示效果。
"空"表示在当前平台上不支持的字符。
常用的货币符号与ascii码对应表
货币符号
对应的ASCII码
¢
162
£
163
¥
165
€
128
24调整计算机默认格式
控制面板-区域和语言选项（或者叫区域设置）可以设置。
25百分比格式控制符
以百分比的形式列出
控制符
输入
显示
说明
0.?
?
%
0.005
0.5%
最多两位小数，小数点对齐
0.?
?
%
0.1234
0.12%
26真分数、科学计数法格式控制符
在分数线/前面添加空格表示真分数表示，如果没有空格就是假分数表示；E+表示正指数添加+号，负指数添加-号；E-只有负指数才添加-号。
控制符
输入
显示
说明
#?
/?
1.25
11/4
真分数格式
0.00E+00
123456
1.23E+05
指数带正号
0.00E+00
-123456
-1.23E+05
科学计数法
0.00E-00
-123456
-1.23E05
指数不要正号
0.00E-00
0.123456
1.23E-01
负指数
27日期时间格式控制符
控制符号如下：
年
月
日
时
分
秒
星期
Y
M
D
H
M
S
D
我们发现，M可以表示月，也可表示分，怎么区分呢？
如果M在Y之后，或者D之前，表示月；如果M在H之后，或者S之前表示分。
符号
意义
yy
将年份显示为00~99
yyyy
1900~9999
m
将月显示为1~12
mm
月份01-12
mmm
月显示Jan~Dec
mmmm
January~December
mmmmm
J~D该月首字母
d
0~31
dd
01~31
ddd
Sun~Sat
dddd
Sunday~Saturday
h
0-23
hh
00-23
m
0-59
mm
00-59
s
0-59
ss
00-59
h:
mmAM/PM
如：
6:
30PM
h:
mm:
ssA/P
如：
7:
15:
47A
[h]:
mm
按小时计算一段时间，如967017:
58（表示：
2010-4-249:
58:
##）
[mm]:
ss
按分钟计算一段时间
[ss]
按秒计算一段时间
h:
mm:
ss.00
以0.01秒计算一段时间
三、排序、筛选、分类汇总
28单个关键字的排序
选中，tab键移动，排序按钮。
或者，选中，数据-排序，
降序顺序：
错误值、逻辑值、字母、数字、空白单元格；
升序排列：
数字、字母、逻辑值、错误值、空白单元格。
汉字有两种排序方法：
字母排序和笔划排序，点选项可以设置。
29多个关键字排序
三个以内的关键字排序好办，三个以上的如何办呢？
比如
关键字序
E
B
F
G
A
C
D
排序
升
降
升
升
降
升
升
排序的方法就是先排最后一个D关键字的顺序，接着C、A……，最后排第一个关键字E的顺序。
30自定义序列
工具-选项，
切换到自定义序列，具体设置略。
31自动筛选
数据-筛选-自动筛选（大家太常用了，略）
32取消自动筛选
数据-筛选-自动筛选（呵呵）
33自定义筛选
先自动筛选，点按钮，选自动筛选。
如对话框所示，略。
34高级筛选
打开附件sheet1，如表：
专业
班级
年级
学号
姓名
原始成绩
财务管理
财务200703
2007
20073495
王**
572
例：
要求筛选出满足下面所有条件的四六级成绩。
●金融、市场专业的
●2006、2007年级的
●姓赵、钱、孙、李、王的
●成绩要求大于或等于520，并且小于530分的
●学号以6、8结尾
解：
打开sheet2，输入条件。
数据-筛选-高级筛选。
按照对话框中提示的操作就可以了。
注意：
同行的表示并且的关系；通列的表示或者的关系。
35分类汇总
分类字段必须排序，数据-分类汇总。
36删除分类汇总
数据-分类汇总，点全部删除。
四、数据透视表
37数据透视表（数据-数据透视表）略
五、公式与函数
38输入公式
以等号“=”、加号“+”、减号“-”开头，输入公式，回车计算。
可以进行错误检查，工具-错误检查。
39绝对引用、相对引用
绝对引用表示不管任何地方发生了什么变化，都是引用那个地方的，不再改变。
$A$1表示不管如何变，我都是引用A1这个单元格的数据。
相对引用表示当序列填充的时候会根据相对的位置移动而变化。
如A1，向右移动一个，变成B1，再向下移动一个，变成B2
混合引用。
如$A1，表示绝对引用A列，相对引用1行。
40单元格的引用方法
长路径：
sheet1!
A2；短路径：
A2。
冒号（：
）表示选取连续区域；逗号（，）表示并集；空格表示交集；
41输入函数
可以自己手动写入，也可以：
插入-函数，来完成。
插入函数的快捷键是shift+F3。
42按步计算嵌套公式
有的公式运算步骤十分复杂，我们希望看到它的每一个运算步骤的结果。
例如：
姓名
英语
语文
数学
优良？
马标行
50
80
68
=IF（SUM（B2:
D2）>240,"优","良"）
工具-公式审核-公式求值，
43常见的错误
错误值
原因
解决方法
####！
数据太太显示不完
扩大列宽
#DIV/0
除以零错误
除数不能为0
#NAME?
未知函数
函数名称写错了
#NULL!
定义域出错
比如，反正弦函数定义域[-1,1]
#NUM!
参数不能接受，或者返回结果极大
修改参数
#REF!
引用无效单元格
检查引用的单元格
#VALUE!
参数的类型错误
修改参数类型
还可以打开公式审核来查看具体错误。
工具-公式审核-显示公式审核工具栏。
六、练习题
1学生成绩表中，如何使不及格的用红色表示，及格成绩绿色表示，最多两位小数，去掉无用的0，小数点对齐？
升级会员 