偶看新闻普瑞森西服:EXCEL高级应用技巧
来源：百度文库 编辑：偶看新闻 时间：2024/05/04 18:38:43
EXCEL高级应用技巧【整理】 1、单元格=当天是“星期几”： =TEXT(NOW(),"aaaa") 单元格=当天日期及时间： =now() 单元格中的日期是“星期几”： 单元格B2中如为日期“2010-10-1”（注意日期格式）， 在B3中输入：=TEXT(B2,"aaaa") 则B3中获得：星期五 2、设置单元格编辑权限(保护部分单元格)： 先选定所有单元格,点"格式"->"单元格"->"保护",取消"锁定"前面的"√"。 再选定你要保护的单元格, 【点“编辑”>“定位”>“公式”】（“【】内步骤可省略，结果是保护的单元格可看到公式不能修改”） 点"格式"->"单元格"->"保护",在"锁定"前面打上"√"。 点"工具"->"保护"->"保护工作表",输入两次密码,点两次"确定"即可。
3、想要移动某一个或几个单元格到表中某一位置： 选中它们，按下Shift键同时拖到要去的位置，待黑光标变成“I”（纵向）或者“H”（横向）形状时，放下即可，绝不会复盖周围的记录，它会挤进去，很方便的；
4. 在不连续的单元格中输入同一内容： 选中你要输入的单元格,在最后的单元格中输入内容,按Ctrl+Ente即可! 5. 计算日期的公式用法：=days360()
我常用来计算年龄，比如b1输入出生年月日，在目标格内可以用这个公式来计算年龄：=days360(b1,today())/360,目标格格式为数字即可。 同时我们计算月份也可用这个公式：=days360(b1,today())/30 6. Excel中Ctrl+End是移动到工组表的最后一个非空单元格
Excel中Ctrl+↓是移动到当前列的最后一个非空单元格（当前列为连续）
Excel中双击任意单元格的下边框也能移动到当前列的最后一个非空单元格（当前列为连续），上下左右边框都可以有相应反应；
7. 输入人名时使用“分散对齐”： 将名单输好后，选中该列，点击“格式--单元格--对齐”，在“水平对齐”中选择“分散对齐”，最后将列宽调整到最合适的宽度，整齐美观的名单就做好了。 8.由身份证号码直接导出“出生年月日”： 假设输入身份证号码的单元格为“C3”，将待出现出生年月日的单元格设置为“日期”格式，并输入： =IF(LEN(C3)=15,DATE(MID(C3,7,2),MID(C3,9,2),MID(C3,11,2)),IF(LEN(C3)=18,DATE(MID(C3,7,4),MID(C3,11,2),MID(C3,13,2)),"号码有错")) 由身份证号码中自动提取出性别： 若身份证所在单元格E4为'342701197001232521（若后三位显示为0，则在最前加半角的“'”或设为文本格式）
选中F4单元格输入=If(mid(e4,17,1)/2=trunc(mid(e4,17,1)/2),"女","男")
mid函数 返回文本串中从指定位置开始的一定数目的字符
trunc函数 返回通过舍弃数字的部分或全部小数位数,使数字的小数位数符合指定条件! 10、打印时锁定表头 点击菜单"文件"选择"页面设置"再选"工作表"项,在打印标题下点击"顶端标题行"或"左端标题行"右面的那文本框处输入"$1:$4"（数字4根据表头的行数进行调整）即可以每页都是固定的标题了,不管有多少行.左端标题行用法相同。 11、excel中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色 比如： A1〉1时，C1显示红色 0“条件格式”，条件1设为： 公式 =A1=1 2、点“格式”->“字体”->“颜色”，点击红色后点“确定”。 条件2设为： 公式 =AND(A1>0,A1<1) 3、点“格式”->“字体”->“颜色”，点击绿色后点“确定”。 条件3设为： 公式 =A1<0 点“格式”->“字体”->“颜色”，点击黄色后点“确定”。 4、三个条件设定好后，点“确定”即出。 12、建立下拉列表按钮： 选定你要设置下拉列表的单元格， 点“数据”->“有效性”->“设置”， 在“允许”下面选择“序列”，在“来源”框中输入你的下拉列表内容，各项之间用半角逗号隔开， 如：A,B,C,D 选中“提供下拉前头”，点“确定”。 建立分类下拉列表填充项 我们常常要将企业的名称输入到表格中，为了保持名称的一致性，利用“数据有效性”功能建了一个分类下拉列表填充项。 1.在Sheet2中，将企业名称按类别（如“工业企业”、“商业企业”、“个体企业”等）分别输入不同列中，建立一个企业名称数据库。
2.选中A列（“工业企业”名称所在列），在“名称”栏内，输入“工业企业”字符后，按“回车”确认。 仿照上面的操作，将B、C……列分别命名为“商业企业”、“个体企业”…… 3.切换到Sheet1中，选中需要输入“企业类别”的列（如C列），执行“数据→有效性”命令，打开“数据有效性”对话框。在“设置”标签中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下面的“来源”方框中，输入“工业企业”，“商业企业”，“个体企业”……序列（各元素之间用英文逗号隔开），确定退出。 再选中需要输入企业名称的列（如D列），再打开“数据有效性”对话框，选中“序列”选项后，在“来源”方框中输入公式：=INDIRECT（C1），确定退出。 4.选中C列任意单元格（如C4），单击右侧下拉按钮，选择相应的“企业类别”填入单元格中。然后选中该单元格对应的D列单元格（如D4），单击下拉按钮，即可从相应类别的企业名称列表中选择需要的企业名称填入该单元格中。
2.在按住Ctrl键的同时，用鼠标在不需要衬图片的单元格（区域）中拖拉，同时选中这些单元格（区域）。 3.按“格式”工具栏上的“填充颜色”右侧的下拉按钮，在随后出现的“调色板”中，选中“白色”。经过这样的设置以后，留下的单元格下面衬上了图片，而上述选中的单元格（区域）下面就没有衬图片了（其实，是图片被“白色”遮盖了）。 提示：衬在单元格下面的图片是不支持打印的。
3、想要移动某一个或几个单元格到表中某一位置： 选中它们，按下Shift键同时拖到要去的位置，待黑光标变成“I”（纵向）或者“H”（横向）形状时，放下即可，绝不会复盖周围的记录，它会挤进去，很方便的；
4. 在不连续的单元格中输入同一内容： 选中你要输入的单元格,在最后的单元格中输入内容,按Ctrl+Ente即可! 5. 计算日期的公式用法：=days360()
我常用来计算年龄，比如b1输入出生年月日，在目标格内可以用这个公式来计算年龄：=days360(b1,today())/360,目标格格式为数字即可。 同时我们计算月份也可用这个公式：=days360(b1,today())/30 6. Excel中Ctrl+End是移动到工组表的最后一个非空单元格
Excel中Ctrl+↓是移动到当前列的最后一个非空单元格（当前列为连续）
Excel中双击任意单元格的下边框也能移动到当前列的最后一个非空单元格（当前列为连续），上下左右边框都可以有相应反应；
7. 输入人名时使用“分散对齐”： 将名单输好后，选中该列，点击“格式--单元格--对齐”，在“水平对齐”中选择“分散对齐”，最后将列宽调整到最合适的宽度，整齐美观的名单就做好了。 8.由身份证号码直接导出“出生年月日”： 假设输入身份证号码的单元格为“C3”，将待出现出生年月日的单元格设置为“日期”格式，并输入： =IF(LEN(C3)=15,DATE(MID(C3,7,2),MID(C3,9,2),MID(C3,11,2)),IF(LEN(C3)=18,DATE(MID(C3,7,4),MID(C3,11,2),MID(C3,13,2)),"号码有错")) 由身份证号码中自动提取出性别： 若身份证所在单元格E4为'342701197001232521（若后三位显示为0，则在最前加半角的“'”或设为文本格式）
选中F4单元格输入=If(mid(e4,17,1)/2=trunc(mid(e4,17,1)/2),"女","男")
mid函数 返回文本串中从指定位置开始的一定数目的字符
trunc函数 返回通过舍弃数字的部分或全部小数位数,使数字的小数位数符合指定条件! 10、打印时锁定表头 点击菜单"文件"选择"页面设置"再选"工作表"项,在打印标题下点击"顶端标题行"或"左端标题行"右面的那文本框处输入"$1:$4"（数字4根据表头的行数进行调整）即可以每页都是固定的标题了,不管有多少行.左端标题行用法相同。 11、excel中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色 比如： A1〉1时，C1显示红色 0
2.选中A列（“工业企业”名称所在列），在“名称”栏内，输入“工业企业”字符后，按“回车”确认。 仿照上面的操作，将B、C……列分别命名为“商业企业”、“个体企业”…… 3.切换到Sheet1中，选中需要输入“企业类别”的列（如C列），执行“数据→有效性”命令，打开“数据有效性”对话框。在“设置”标签中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下面的“来源”方框中，输入“工业企业”，“商业企业”，“个体企业”……序列（各元素之间用英文逗号隔开），确定退出。 再选中需要输入企业名称的列（如D列），再打开“数据有效性”对话框，选中“序列”选项后，在“来源”方框中输入公式：=INDIRECT（C1），确定退出。 4.选中C列任意单元格（如C4），单击右侧下拉按钮，选择相应的“企业类别”填入单元格中。然后选中该单元格对应的D列单元格（如D4），单击下拉按钮，即可从相应类别的企业名称列表中选择需要的企业名称填入该单元格中。
提示：在以后打印报表时，如果不需要打印“企业类别”列，可以选中该列，右击鼠标，选“隐藏”选项，将该列隐藏起来即可。
13、阿拉伯数字转换为人民币大写金额： 假定你要在B1输入阿拉佰数字,C1转换成中文大写金额(含元角分),请在C1单元格输入如下公式: =SUBSTITUTE(SUBSTITUTE(IF(-RMB(B1),IF(B1>0,,"负")&TEXT(INT(ABS(B1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(B1,2),2),"[dbnum2]0角0分;;整"),),"零角",IF(B1^2<1,,"零")),"零分","整") 14、表头下面衬张图片 为工作表添加的背景，是衬在整个工作表下面的，能不能只衬在表头下面呢? 1.执行“格式→工作表→背景”命令，打开“工作表背景”对话框，选中需要作为背景的图片后，按下“插入”按钮，将图片衬于整个工作表下面。2.在按住Ctrl键的同时，用鼠标在不需要衬图片的单元格（区域）中拖拉，同时选中这些单元格（区域）。 3.按“格式”工具栏上的“填充颜色”右侧的下拉按钮，在随后出现的“调色板”中，选中“白色”。经过这样的设置以后，留下的单元格下面衬上了图片，而上述选中的单元格（区域）下面就没有衬图片了（其实，是图片被“白色”遮盖了）。 提示：衬在单元格下面的图片是不支持打印的。