EXCEL日常使用.docx
《EXCEL日常使用.docx》由会员分享,可在线阅读,更多相关《EXCEL日常使用.docx(10页珍藏版)》请在冰豆网上搜索。
EXCEL日常使用
一、输入位数较多的数字
如果向Excel中输入位数比较多的数值(如身份证号码),则系统会将其转为科学计数的格式,与我们的输入原意不相符,解决方法是:
1.将该单元格中的数值设置成“文本”格式。
如果用命令的方法直接去设置,也可以实现,但操作很慢。
2.其实我们在输入这些数值时,只要在数值的前面加上一个小“'”(注意:
'必须是在英文状态下输入)。
二、?
格格式/数字/自定义,类型下面输入:
####"."##
将文本“2004.01.02”转换为日期格式:
2004-1-2
=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))
三、同名查找
在姓名之后插入一列,输入公式:
“=COUNTIF(C$2:
C$12,C2)”(不含引号,同时,行号前的“$”表示对行的绝对引用,不可漏掉),表示在C列从第2个单元格到第12个单元格中(当然这里只有11个姓名)对C2单元格中的内容(即“张三”)进行计数,如果没有重名,结果自然为1,如果有两个重名,结果为2……然后再选择D2单元格,双击(或拖动)D2单元格右下角的填充柄对D3:
D12进行自动填充。
四、让不同类型数据用不同颜色显示
在工资表中,如果想让大于等于2000元的工资总额以“红色”显示,大于等于1500元的工资总额以“蓝色”显示,低于1000元的工资总额以“棕色”显示,其它以“黑色”显示,我们可以这样设置。
1.打开“工资表”工作簿,选中“工资总额”所在列,执行“格式→条件格式”命令,打开“条件格式”对话框。
单击第二个方框右侧的下拉按钮,选中“大于或等于”选项,在后面的方框中输入数值“2000”。
单击“格式”按钮,打开“单元格格式”对话框,将“字体”的“颜色”设置为“红色”。
2.按“添加”按钮,并仿照上面的操作设置好其它条件(大于等于1500,字体设置为“蓝色”;小于1000,字体设置为“棕色”)。
3.设置完成后,按下“确定”按钮。
五、变文本为数字
1.在空白的单元格中填入数字1;
2.然后选中这个单元格,执行“复制”命令;
3.再选中所要转换的范围,选择“选择性粘贴”中的“乘”。
六、将数字设为文本格式
1.选择含有要设置成文本格式的数字单元格;
2.单击“格式→单元格”命令,然后单击“数字”选项卡();
3.在“分类”列表中,单击“文本”,然后再单击“确定”;
4.单击每个单元格,按F2键,然后再按Enter键重新输入数据。
七、单元格数据分列
1.选中要进行分列的列;
2.点击数据-分列按钮;
3.选中分隔符号,点下一步;
4.根据具体要求选择不同的分列方式,本例中使用“,”号进行分列。
5.设定分列后每一列的数据格式,(数字数据中首位为0的一列要设成文本格式,身份证号等长数字数据也要设成文本格式。
)
6.点击完成即可完成数据的分列。
八、合并两个单元格内容
◆技巧一:
用连字符“&”来合并单元格内容
例如想把A、B、C列合并到D列中,操作方法如下:
1、只需要在D1单元格中输入公式:
=A1&B1&C1即可;
2、再次选中D1单元格,移动鼠标,当鼠标形状变成黑色实心十字时(即利用“填充柄”将上述公式复制到D列下面的单元格中),然后按住鼠标左键向下拖动,这样A、B、C列的内容即被合并到D列对应的单元格中。
3、选中D列,执行“复制”操作,然后再次选中D列,执行“编辑→选择性粘贴”命令,打开“选择性粘贴”对话框,选中其中的“数值”选项,按下“确定”按钮,D列的内容就是合并后的结果,而不是公式。
另外,如果希望在合并文本之间添加空格或者中划线(-),请键入&""&或者&"-"&。
第一步就需要改成=A1&"-"&B1&"-"&C1。
◆技巧二:
利用CONCATENATE函数CONCATENATE函数可将多个单元格的文本合并到一个单元格中
如上例:
1、在D1单元格中输入公式:
=CONCATENATE(A1,B1,C1)即可;然后按照第2、3步依次操作即可!
这里如果想加中划线(-),可以这样输入:
=CONCATENATE(A1,&"-"&,B1,&"-"&,C1)学技巧提示:
完成第1、2步的操作,合并效果已经实现,但此时如果删除A、B、C列,公式会出现错误。
故须进行第3步操作,将公式转换为不变的“值”。
九、如何检查Excel公式是否正确
追踪引用单元格
1.选中公式所在单元格(选中公式所在单元格,然后通过查看该公式引用了哪些单元格,这里公式比较简单,不用查看你可以清楚的知道,但是比较复杂的公式很难判断是否使用正确。
)
2.公式-追踪引用单元格(点击公式-追踪引用单元格,被引用的单元格内容都会指向公式。
)
3.公式-追踪从属单元格(点击公式-追踪从属单元格,可以反向证明公式是否正确。
)
4.公式-移去箭头(点击公式-移去箭头,就能把上面的箭头给去除了。
)
5.选中公式所在单元格
6.使用快捷键(通过快捷键Ctrl+【,检查Excel公式是否正确。
)
一十、用于输入、编辑、设置格式和计算数据的快捷键
完成单元格输入并选取下一个单元:
Enter
在单元格中换行:
Alt+Enter
用当前输入项填充选定的单元格区域:
Ctrl+Enter
完成单元格输入并向上选取上一个单元格:
Shift+Enter
完成单元格输入并向右选取下一个单元格:
Tab
完成单元格输入并向左选取上一个单元格:
Shift+Tab
取消单元格输入:
Esc
向上、下、左或右移动一个字符:
箭头键
移到行首:
Home
重复上一次操作:
F4或Ctrl+Y
由行列标志创建名称:
Ctrl+Shift+F3
向下填充:
Ctrl+D
向右填充:
Ctrl+R
定义名称:
Ctrl+F3
插入超链接:
Ctrl+K
激活超链接:
Enter(在具有超链接的单元格中)
输入日期:
Ctrl+;(分号)
输入时间:
Ctrl+Shift+:
(冒号)
显示清单的当前列中的数值下拉列表:
Alt+向下键
显示清单的当前列中的数值下拉列表:
Alt+向下键
撤销上一次操作:
Ctrl+Z
一十一、出现Excel公式错误值该如何解决
步骤/方法
1.#####!
如果单元格所含的数字、日期或时间比单元格宽,或者单元格的日期时间公式产生了一个负值,就会产生#####!
。
这个看起来比较简单,大家应该都了解吧。
◎解决方法:
如果单元格所含的数字、日期或时间比单元格宽,可以通过拖动列表之间的宽度来修改列宽。
如果使用的是1900年的日期系统,那么Excel中的日期和时间必须为正值。
如果公式正确,也可以将单元格的格式改为非日期和时间型来显示该值。
2.#VALUE!
当使用错误的参数或运算对象类型时,或者当公式自动更正功能不能更正公式时,将产生错误值#VALUE!
。
这其中主要包括3点原因。
1)在需要数字或逻辑值时输入了文本,Excel不能将文本转换为正确的数据类型。
◎解决方法:
确认公式或函数所需的运算符或参数正确,并且公式引用的单元格中包含有效的数值。
例如:
如果单元格A1包含一个数字,单元格A2包含文本,则公式="A1+A2"将返回错误值#VALUE!
。
可以用SUM工作表函数将这两个值相加(SUM函数忽略文本):
=SUM(A1:
A2)。
2)将单元格引用、公式或函数作为数组常量输入。
◎解决方法:
确认数组常量不是单元格引用、公式或函数。
3)赋予需要单一数值的运算符或函数一个数值区域。
◎解决方法:
将数值区域改为单一数值。
修改数值区域,使其包含公式所在的数据行或列。
3.#DIV/O!
当公式被零除时,将会产生错误值#DIV/O!
。
在具体操作中主要表现为以下两种原因。
1)在公式中,除数使用了指向空单元格或包含零值单元格的单元格引用(在Excel中如果运算对象是空白单元格,Excel将此空值当作零值)。
◎解决方法:
修改单元格引用,或者在用作除数的单元格中输入不为零的值。
2)输入的公式中包含明显的除数零,例如:
公式=1/0。
◎解决方法:
将零改为非零值。
4.#N/A当在函数或公式中没有可用数值时,将产生错误值#N/A。
◎解决方法:
如果工作表中某些单元格暂时没有数值,请在这些单元格中输入"#N/A",公式在引用这些单元格时,将不进行数值计算,而是返回#N/A。
5.#REF!
删除了由其他公式引用的单元格,或将移动单元格粘贴到由其他公式引用的单元格中。
当单元格引用无效时将产生错误值#REF!
。
◎解决方法:
更改公式或者在删除或粘贴单元格之后,立即单击"撤消"按钮,以恢复工作表中的单元格。
6.#NUM!
当公式或函数中某个数字有问题时将产生错误值#NUM!
。
1)在需要数字参数的函数中使用了不能接受的参数。
◎解决方法:
确认函数中使用的参数类型正确无误。
2)由公式产生的数字太大或太小,Excel不能表示。
◎解决方法:
修改公式,使其结果在有效数字范围之间。
7.#NULL!
使用了不正确的区域运算符或不正确的单元格引用。
当试图为两个并不相交的区域指定交叉点时将产生错误值#NULL!
。
◎解决方法:
如果要引用两个不相交的区域,请使用联合运算符逗号(,)。
公式要对两个区域求和,请确认在引用这两个区域时,使用逗号。
如果没有使用逗号,Excel将试图对同时属于两个区域的单元格求和,由于A1:
A13和c12:
c23并不相交,它们没有共同的单元格所以就会出错。
一十二、将WPS/Word表格转换为Excel工作表
1.启动WPS/Word,打开WPS/Word文档,拖动鼠标,选择整个表格,再在“编辑”菜单中选择“复制”命令;
2.启动Excel,打开Excel工作表,单击目标表格位置的左上角单元格,再在“编辑”菜单中选择“粘贴”命令。
(注:
若需将老版本的WPS表格转换为Excel工作表,应先将其用WPS97/WPS2000/WPSOffice或Word打开,再进行转换。
)
一十三、在Excel中快速插入Word表格
1.打开Word表格所在的文件;
2.打开要处理Word表格的Excel文件,并调整好两窗口的位置,以便看见表格和要插入表格的区域;
3.选中Word中的表格;
4.按住鼠标左键,将表格拖到Excel窗口中,松开鼠标左键将表格放在需要的位置即可。
一十四、数据透视表基本操作实战技巧
利用数据透视表可以快速汇总大量数据并进行交互,还可以深入分析数值数据,并回答一些预计不到的数据问题。
◆创建方法一
1.打开工作表,选中数据区域中任意单元格,单击“插入”选项卡“表”组中“数据透视表”按钮下方的下拉按钮,在弹出的下拉菜单中选择“数据透视表”选项;
2.弹出“创建数据透视表”对话框,单击“表/区域”文本框右侧的折叠按钮,选择数据区域;
3.在“数据透视表字段列表”任务窗格的“选择要添加到报表的字段”选项区中选中要在数据透视表中显示的字段;
4.用户可根据不同的需求,创建不同的透视表。
(EXCEL中实际操作)
一十五、EXCEL中常用函数及使用方法
◆SUMIF
用途:
根据指定条件对若干单元格、区域或引用求和。
语法:
SUMIF(range,criteria,sum_range)
参数:
Range为用于条件判断的单元格区域,Criteria是由数字、逻辑表达式等组成的判定条件,Sum_range为需要求和的单元格、区域或引用。
◆COUNTIF
主要功能:
统计某个单元格区域中符合指定条件的单元格数目。
使用格式:
COUNTIF(Range,Criteria)
参数说明:
Range代表要统计的单元格区域;Criteria表示指定的条件表达式。
应用举例:
在C17单元格中输入公式:
=COUNTIF(B1:
B13,">=80"),确认后,即可统计出B1至B13单元格区域中,数值大于等于80的单元格数目。
特别提醒:
允许引用的单元格区域中有空白单元格出现。
◆SEARCH或SEARCHB
【用途】返回从start_num开始首次找到特定字符或文本串的位置编号。
其中SEARCH以字符数为单位,SEARCHB以字节数为单位。
【语法】SEARCH(find_text,within_text,start_num),SEARCHB(find_text,within_text,start_num)。
【参数】Find_text是要查找的文本,可以使用通配符,包括问号“?
”和星号“*”。
其中问号可匹配任意的单个字符,星号可匹配任意的连续字符。
如果要查找实际的问号或星号,应当在该字符前键入波浪线“~”。
Within_text是要在其中查找find_text的文本。
Start_num是within_text中开始查找的字符的编号。
如果忽略start_num,则假定其为1。
【实例】如果A1=医用纱布块,则公式“=SEARCH("纱",A1)”返回3,=SEARCHB("纱",A1)返回5。
◆LEFT或LEFTB
【用途】根据指定的字符数返回文本串中的第一个或前几个字符。
此函数用于双字节字符。
【语法】LEFT(text,num_chars)或LEFTB(text,num_bytes)。
【参数】Text是包含要提取字符的文本串;Num_chars指定函数要提取的字符数,它必须大于或等于0。
Num_bytes按字节数指定由LEFTB提取的字符数。
【实例】如果A1=医用纱布块,则LEFT(A1,2)返回“医用”,LEFTB(A1,2)返回“医”。
一十六、EXCEL单元格格式自定义使用部分
单元格格式的自定义详解
1、”G/通用格式”:
以常规的数字显示,相当于”分类”列表中的”常规”选项。
例:
代码:
”G/通用格式”。
10显示为10;10.1显示为10.1。
2、“#”:
数字占位符。
只显有意义的零而不显示无意义的零。
小数点后数字如大于”#”的数量,则按”#”的位数四舍五入。
例:
代码:
”###.##”,12.1显示为12.10;12.1263显示为:
12.133、”0”:
数字占位符。
如果单元格的内容大于占位符,则显示实际数字,如果小于点位符的数量,则用0补足。
例:
代码:
”00000”。
1234567显示为1234567;123显示为00123代码:
”00.000”。
100.14显示为100.140;1.1显示为01.100
4、”@”:
文本占位符,如果只使用单个@,作用是引用原始文本,要在输入数字数据之后自动添加文本,使用自定义格式为:
”文本内容”@;要在输入数字数据之前自动添加文本,使用自定义格式为:
@”文本内容”。
@符号的位置决定了Excel输入的数字数据相对于添加文本的位置。
如果使用多个@,则可以重复文本。
例:
代码”;;;"集团"@"部"“,财务显示为:
集团财务部代码”;;;@@@“,财务显示为:
财务财务财务
5、”*”:
重复下一次字符,直到充满列宽。
例:
代码:
”@*-”。
”ABC”显示为”ABC-------------------”可就用于仿真密码保护:
代码”**;**;**;**”,123显示为:
************
6、”,”:
千位分隔符例:
代码”#,###“,12000显示为:
12,0007、\:
用这种格式显示下一个字符。
"文本",显示双引号里面的文本。
“\”:
显示下一个字符。
和“”””用途相同都是显示输入的文本,且输入后会自动转变为双引号表达。
例:
代码"人民币"#,##0,,"百万",与\人民币#,##0,,\百万,输入1234567890显示为:
人民币1,235百万
8、“?
”:
数字占位符。
在小数点两边为无意义的零添加空格,以便当按固定宽度时,小数点可对齐,另外还用于对不等到长数字的分数例:
分别设置单元格格式为”?
?
.?
?
”和”?
?
?
.?
?
?
”,对齐结果如下:
输入12.1212显示12.1212.121
9、颜色:
用指定的颜色显示字符。
可有八种颜色可选:
红色、黑色、黄色,绿色、白色、兰色、青色和洋红。
例:
代码:
“[青色];[红色];[黄色];[兰色]”。
显示结果为正数为青色,负数显示红色,零显示黄色,文本则显示为兰色[颜色N]:
是调用调色板中颜色,N是0~56之间的整数。
例:
代码:
“[颜色3]”。
单元格显示的颜色为调色板上第3种颜色。
10、条件:
可以单元格内容判断后再设置格式。
条件格式化只限于使用三个条件,其中两个条件是明确的,另个是“所有的其他”。
条件要放到方括号中。
必须进行简单的比较。
例:
代码:
“[>0]”正数”;[=0];”零”;负数”。
显示结果是单元格数值大于零显示正数,等于0显示零,小于零显示“负数”。
11、“!
”:
显示“"”。
由于引号是代码常用的符号。
在单元格中是无法用"""来显示出来“"”。
要想显示出来,须在前加入“!
”例:
代码:
“#!
"”。
“10”显示“10"”代码:
“#!
"!
"”。
“10”显示“10""”
12、时间和日期代码常用日期和时间代码“YYYY”或“YY”:
按四位(1900~9999)或两位(00~99)显示年“MM”或“M”:
以两位(01~12)或一位(1~12)表示月。
“DD”或“D”:
以两位(01~31)或一位(1-31)来表示天。
例:
代码:
“YYYY-MM-DD”。
2005年1月10日显示为:
“2005-01-10”