文秘办公表格公式一览.docx

《文秘办公表格公式一览.docx》由会员分享，可在线阅读，更多相关《文秘办公表格公式一览.docx（26页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、该文本为Word版，下载可编辑文秘办公表格公式一览 行政、人事、财务等各部门都会用到的各种办公表格，那么办公表格常用技巧有哪些?下面是我给大家整理的文秘办公表格公式一览(最新)，欢迎大家来阅读。 文秘办公表格公式 一、word文字排版 1、输入与编辑文档：文档的操作 输入文本 修改文本 输入文本技巧 撤销和恢复。 2、文档基本排版操作：设置字体格式 设置段落对齐 设置段落缩进 设置文档间距 边框和底纹。 3、文档高级排版操作：使用格式刷 分栏排版 使用样式 使用模板 妙用中文版式 其他实用技巧。 4、在文档中制作表格：创建表格 输入表格数据 编辑表格 处理表格数据 美化表格。 5、图文混排：

2、插入图片 编辑图片 使用艺术字 插入自选图形插入图示妙用文本框。 6、长文档排版： 使用文档视图 书签 目录 索引 脚注与尾注 其他编排技巧 7、页面设置：设置页面版式 背景和主题 页眉和页脚 分页与分节。 8、综合实例。 二、Excel电子表格 1、数据的输入(文本数据输入，分数输入，有规律数据输入，)。 2、数据输入合法性的判断。 3、格式设置。 4、数据的公式计算与函数计算(求和，求平均，计数，求最大，求最小，假设函数)。 5、数据排序、筛选、分类汇总，数据透视图与数据透视表。 6、图表及打印功能。 三、办公表格公式 1、 查找重复内容公式：=IF(COUNTIF(A:A,A2)1,重复

3、,)。 2、 用出生年月来计算年龄公式：=TRUNC(DAYS360(H6,2023/8/30,FALSE)/360,0)。 3、 从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E2,7,4),/,MID(E2,11,2),/,MID(E2,13,2)。 4、 从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,男,女),IF(MOD(MID(C2,17,1),2)=1,男,女)公式内的“C2”代表的是输入身份证号码的单元格。 1、求和： =SUM(K2:K56) 对K2到K5

4、6这一区域进行求和; 2、平均数： =AVERAGE(K2:K56) 对K2 K56这一区域求平均数; 3、排名： =RANK(K2，K$2:K$56) 对55名学生的成绩进行排名; 4、等级： =IF(K2=85,优,IF(K2=74,良,IF(K2=60,及格,不及格) 5、学期总评： =K2_0.3+M2_0.3+N2_0.4 假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩; 6、最高分： =MAX(K2:K56) 求K2到K56区域(55名学生)的最高分; 7、最低分： =MIN(K2:K56) 求K2到K56区域(55名学生)的最低分; 8、分数段人数统

5、计： (1) =COUNTIF(K2:K56,100) 求K2到K56区域100分的人数;假设把结果存放于K57单元格; (2) =COUNTIF(K2:K56,=95)-K57 求K2到K56区域9599.5分的人数;假设把结果存放于K58单元格; (3)=COUNTIF(K2:K56,=90)-SUM(K57:K58) 求K2到K56区域9094.5分的人数;假设把结果存放于K59单元格; (4)=COUNTIF(K2:K56,=85)-SUM(K57:K59) 求K2到K56区域8589.5分的人数;假设把结果存放于K60单元格; (5)=COUNTIF(K2:K56,=70)-SUM(

6、K57:K60) 求K2到K56区域7084.5分的人数;假设把结果存放于K61单元格; (6)=COUNTIF(K2:K56,=60)-SUM(K57:K61) 求K2到K56区域6069.5分的人数;假设把结果存放于K62单元格; (7) =COUNTIF(K2:K56,60) 求K2到K56区域60分以下的人数;假设把结果存放于K63单元格; 说明：COUNTIF函数也可计算某一区域男、女生人数。 如：=COUNTIF(C2:C351,男) 求C2到C351区域(共350人)男性人数; 9、优秀率： =SUM(K57:K60)/55_100 10、及格率： =SUM(K57:K62)/5

7、5_100 11、标准差： =STDEV(K2:K56) 求K2到K56区域(55人)的成绩波动情况(数值越小，说明该班学生间的成绩差异较小，反之，说明该班存在两极分化); 12、条件求和： =SUMIF(B2:B56,男，K2:K56) 假设B列存放学生的性别，K列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和; 13、多条件求和： =SUM(IF(C3:C322=男,IF(G3:G322=1,1,0) 假设C列(C3:C322区域)存放学生的性别，G列(G3:G322区域)存放学生所在班级代码(1、2、3、4、5)，则此函数返回的结果表示求一班的男生人数;这是一个数组函数，输完

8、后要按Ctrl+Shift+Enter组合键(产生“”)。“”不能手工输入，只能用组合键产生。 14、根据出生日期自动计算周岁：=TRUNC(DAYS360(D3,NOW( )/360,0) 假设D列存放学生的出生日期，E列输入该函数后则产生该生的周岁。 15、在Word中三个小窍门： 连续输入三个“”可得一条波浪线。 连续输入三个“-”可得一条直线。 连续输入三个“=”可得一条双直线。 一、excel中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比如： A11时，C1显示红色 0 A10时，C1显示黄色 方法如下： 1、单元击C1单元格，点“格式”“条件格式”，条件1设为： 公式

9、 =A1=1 2、点“格式”-“字体”-“颜色”，点击红色后点“确定”。 条件2设为： 公式 =AND(A10,A11) 3、点“格式”-“字体”-“颜色”，点击绿色后点“确定”。 条件3设为： 公式 =A10 点“格式”-“字体”-“颜色”，点击黄色后点“确定”。 4、三个条件设定好后，点“确定”即出。 二、EXCEL中如何控制每列数据的长度并避免重复录入 1、用数据有效性定义数据长度。 用鼠标选定你要输入的数据范围，点数据-有效性-设置，有效性条件设成允许文本长度等于5(具体条件可根据你的需要改变)。 还可以定义一些提示信息、出错警告信息和是否打开中文输入法等，定义好后点确定。 2、用条件

10、格式避免重复。 选定A列，点格式-条件格式，将条件设成“公式=COUNTIF($A:$A,$A1)1”，点格式-字体-颜色，选定红色后点两次确定。 这样设定好后你输入数据如果长度不对会有提示，如果数据重复字体将会变成红色。 三、在EXCEL中如何把B列与A列不同之处标识出来? (一)、如果是要求A、B两列的同一行数据相比较： 假定第一行为表头，单击A2单元格，点“格式”-“条件格式”，将条件设为: “单元格数值” “不等于”=B2 点“格式”-“字体”-“颜色”，选中红色，点两次“确定”。 用格式刷将A2单元格的条件格式向下复制。 B列可参照此方法设置。 (二)、如果是A列与B列整体比较(即相

11、同数据不在同一行)： 假定第一行为表头，单击A2单元格，点“格式”-“条件格式”，将条件设为: “公式”=COUNTIF($B:$B,$A2)=0 点“格式”-“字体”-“颜色”，选中红色，点两次“确定”。 用格式刷将A2单元格的条件格式向下复制。 B列可参照此方法设置。 按以上方法设置后，AB列均有的数据不着色，A列有B列无或者B列有A列无的数据标记为红色字体。 四、EXCEL中怎样批量地处理按行排序 假定有大量的数据(数值)，需要将每一行按从大到小排序，如何操作? 由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。所以，这一问题不能用排序来解决。解决方法如下：

12、 1、假定你的数据在A至E列，请在F1单元格输入公式： =LARGE($A1:$E1,COLUMN(A1) 用填充柄将公式向右向下复制到相应范围。 你原有数据将按行从大到小排序出现在F至J列。如有需要可用“选择性粘贴/数值”复制到其他地方。 注：第1步的公式可根据你的实际情况(数据范围)作相应的修改。如果要从小到大排序,公式改为:=SMALL($A1:$E1,COLUMN(A1) 五、巧用函数组合进行多条件的计数统计 例：第一行为表头，A列是“姓名”，B列是“班级”，C列是“语文成绩”，D列是“录取结果”，现在要统计“班级”为“二”，“语文成绩”大于等于104，“录取结果”为“重本”的人数。统

13、计结果存放在本工作表的其他列。 公式如下： =SUM(IF(B2:B9999=二)_(C2:C9999=104)_(D2:D9999=重本),1,0) 输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号。 六、如何判断单元格里是否包含指定文本? 假定对A1单元格进行判断有无指定文本,以下任一公式均可: =IF(COUNTIF(A1,_指定文本_)=1,有,无) =IF(ISERROR(FIND(指定文本,A1,1),无,有) 求某一区域内不重复的数据个数 例如求A1:A100范围内不重复数据的个数，某个数重复多次出现只算一个。有两种计算方法： 一是利用数组公式： =SU

14、M(1/COUNTIF(A1:A100,A1:A100) 输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号。 二是利用乘积求和函数： =SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100) 七、一个工作薄中有许多工作表如何快速整理出一个目录工作表 1、用宏3.0取出各工作表的名称，方法： Ctrl+F3出现自定义名称对话框，取名为X，在“引用位置”框中输入： =MID(GET.WORKBOOK(1),FIND(,GET.WORKBOOK(1)+1,100) 确定 2、用HYPERLINK函数批量插入连接，方法： 在目录工作表(一般为第一个shee

15、t)的A2单元格输入公式： =HYPERLINK(#INDEX(X,ROW()!A1,INDEX(X,ROW() 将公式向下填充，直到出错为止，目录就生成了。 办公软件使用技巧 1. 同时保存所有打开的Word 文档 有时在同时编辑多个Word文档时，每个文件要逐一保存，既费时又费力，有没有简单的方法呢?在按下Shift键的同时，单击“文件”菜单，大家看，已经多出了“同时保存”和“同时关闭”命令 ，这可就方便多了 2. 巧妙设置文档保护 在用Word 2000/XP打印一份文件时，忽然头儿有要事吩咐，要暂时离开一下，关闭文件吧，既费事又没必要，但又不想辛辛苦苦写的文件被别人破坏了。怎么办呢?执

16、行“工具”菜单中的“保护文档”命令，在弹出的窗口中选择“保护窗体”，然后设上密码。怎么样，光标跑到了文件头，任你怎么移动鼠标 、敲击键盘 就是无法编辑了，不错吧。等回来时，执行“工具”菜单中的“解除文档保护”命令，一切又正常了。 3. 编辑长文件更轻松 相信大家都有这样的经历：在使用Excel 、Word编辑长文档时，有时需要将文章开始的多处内容复制到文章末尾。但通过拖动滚动条来回移动非常麻烦，还会出错。其实只要将鼠标移动到滚动条上面的适当位置，发现鼠标指针变成双箭头，此时按住鼠标左键向下拖动，文档编辑区会被一分为二。你只需在上面编辑区找到文章开头的内容，在下面编辑区找到需要粘贴的位置，这样你

17、就可以复制内容了，而不必来回切换。这种方法特别适合复制相距很远且处数较多的内容。 4. 给WPS 文档添加水印 先用作图软件(画笔工具等)制作一张色彩非常淡的图片(直接将Word的水印效果图“抓”下来也行)，然后打开需要添加水印的WPS文档，执行“格式页面背景设置”命令，打开“设置背景”对话框，切换到“图片”标签下，按“选择图片”按钮，选中刚才制作的图片，并进行适当设置后确定，就可以为文档设置上水印了。 5. 在金山文字中快速打开“页面设置” 在用金山文字2023处理文档时，一个最常用的操作就是设置页面。先执行“视图标尺水平(垂直)标尺”命令，展开“水平(垂直)标尺”打开，然后用鼠标在水平(垂

18、直)标尺上双击，即可快速打开“页面设置”对话框，对页面进行设置了。 6. 在Excel中插入拼音 在我们使用Excel时，常常会遇到十分孤僻的字，但加上拼音解释就能使人看得更明白。具体的方法是：选中想要加入拼音解释的表格，打开Excel的菜单栏中的“格式”菜单，选中“拼音信息/设置”项，在弹出的对话框中的“设置”和“字体”选项中，分别选择拼音的对齐方式和字体，最后选择“拼音信息”子菜单中的“编缉”，即可以在单元格中输入拼音了。要查看拼音，可通过“拼音信息”子菜单中的“显示和隐藏”项显示解释的拼音。 7. 取消“自作聪明”的超级链接 当我们在Word文件中键入网址或信箱的时候，Word会自动为我

19、们转换为超级链接，如果不小心在网址上按一下，就会启动IE进入超级链接。但如果我们不需要这样的功能，就会觉得有些碍手碍脚了。如何取消这种功能呢?在Word中单击“工具自动更正选项”，单击“键入时自动套用格式”选项，将“Internet及网络路径替换为超级链接”项目前的小钩取消。再单击“自动套用格式”选项，将“Internet及网络路径替换为超级链接”项目前的小钩取消。再单击“确定”。这样，以后再输入网址后，就不会转变为超级链接了。 8. 巧设Word启动后的默认文件夹 Word启动后，默认打开的文件夹总是“我的文档”。通过设置，我们可以自定义Word启动后的默认文件夹，步骤如下：(1)单击“工具

20、”菜单中“选项”命令，程序将会弹出“选项”的对话框;(2)在对话框中，选择“文件位置”标签，同时选择“文档”;(3)单击“更改”按钮，打开“更改位置”对话框，在“查找范围”下拉框中，选择你希望设置为默认文件夹的文件夹并单击“确定”按钮;(4)最后单击“确定”按钮，此后Word的默认文件夹就是用户自己设定的文件夹。 9. 在Word中快速输入汉字偏旁部首 作为语文 老师，在用Word编制练习时，常常需要输入汉字的偏旁部首。执行“插入符号”命令，打开“符号”对话框，在“符号”标签下，将字体选定为“标准字体”，“子集”选定为“CJK统一汉字”，你就会发现下面的汉字是按偏旁部首排列的。每一类汉字都是以

21、该类汉字的偏旁部首开始的，选中你需要的(如扌、攵、氵等)偏旁部首，按“插入”按钮就行了。 10. 改变WPSOffice2000启动后的默认纸张和字号 1.进入WPSOffice2000，选择“文件页面设置”，将“纸张规格”设置为16开或B5，并根据需要设置好纸张边距，回车确定。 2.字体字号可根据自己的写作习惯定义一下，比如“宋体、四号”。 3.选择“文件存为模板”，在左面模板类型中选择“通用”型，在右面“模板名”中输入模板名称，单击“确定”。然后退出WPS Office。 4.再次进入WPS Office，选择“文件新建”，单击“通用”模板，单击你刚才输入的模板名称，单击对话框右下角的“设

22、为默认模板”，单击“确定”，大功告成。这样，当你每次进入WPS Office后，纸张就变为16开或B5、字体字号也变为你所需要的了。 Excel办公软件实用技巧 一、在Excel中如何使它像Word一样的自动定时保存文件? 点击“工具”菜单“自动保存”项，设置自动保存文件夹的间隔时间。如果在“工具”菜单下没有“自动保存”菜单项，那么执行“工具”菜单下“加载宏.”选上“自动保存”，“确定”。然后进行设置即可。 二、用Excel做多页的表格时，怎样像Word的表格那样做一个标题。 在Excel的文件菜单-页面设置-工作表-打印标题;可进行顶端或左端标题设置，通过按下折叠对话框按钮后，用鼠标划定范围

23、即可。这样Excel就会自动在各页上加上你划定的部分作为表头。 三、Excel多条件求和函数有哪些? 在Excel里，有哪些函数可以完成多条件求和呢? SUMIF函数和COUNTIF函数用法差不多.多条件的处理方法如下: 1.多项目求和 =SUM(SUMIF(B31:B35,A,C,C31:C35) 2. 03版本可以用SUMPRODUCT函数替代. =SUMPRODUCT(MONTH(A3:A9)=3)_(B3:B9=A)_C3:C9) 3 .07版本可以用SUMIFS替换: =SUMIFS(D2:D11,A2:A11,公司1,B2:B11,人事部) 多条件汇总，也可以用SUMPRODUCT

24、函数完成。 =SUMPRODUCT(条件1 _条件2_条件3_数据列) =sumproduct(a1:a10=财务)_(b1:b10=出纳)_c1:c10)统计A列是财务，同时B列是出纳的C列数据之和 四、快速批量编辑Excel数据 要将Excel工作表某单元格区域中的所有值均减少10%，可采用以下步骤：在某单元格中键入数字“0.9”，复制该单元格，选中该区域，单击“编辑”菜单中的“选择性粘贴”命令，单击“乘”，单击“确定”按钮。 五、彩色Excel表格显示打印两不误 如果将Excel工作表中的数据设置为彩色格式，而打印机为黑白打印机，那么工作表中的彩色区域打印出来的效果可能会不理想。在不更改

25、工作表颜色设置的情况下，可以进行如下设置： 1.在“文件”菜单中，单击“页面设置”命令。 2.单击“工作表”选项，然后选择“打印”框下的“单色打印”项。如果使用的是彩色打印机，选择该选项可减少打印所需时间。 3.单击“确定”按钮。 六、Excel文件打开乱码怎么办? 1:将工作簿另存为SYLK格式 如果Excel文件能够打开，那么将工作簿转换为SYLK格式可以筛选出文档的损坏部分，然后再保存数据。首先，打开需要的工作簿。在“文件”菜单中，单击“另存为”命令。在“保存类型”列表中，单击“SYLK(符号连接)(_.slk)”，然后单击“保存”按钮。关闭目前开启的文件后，打开刚才另存的SYLK版本即可。 2:转换为较早的版本 如果由于启动故障而没有保存Excel工作簿，则最后保存的版本可能不会被损坏。当然，该版本不包括最后一次保存后对文档所作的更改。关闭打开的工作簿，当系统询问是否保存更改时，单击“否”。在“文件”菜单中，单击“打开”命令，双击该工作簿文件即可。 办公软件看似简单，其实花样很多。但其实只要用心多练，效率轻松提升个十倍百倍还真不是问题。只要有肯吃苦的决心，就一定会有所突破。以上这些希望可以对你有所帮助。 第 26 页 共 26 页