Excel常用基本操作技巧.pdf

《Excel常用基本操作技巧.pdf》由会员分享，可在线阅读，更多相关《Excel常用基本操作技巧.pdf（30页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、-1-Excel 常用基本操作技巧汇总Excel 中插入动态日期和时间在 Excel 中是可以插入日期和时间的，但这个日期和时间是静止的。那么我们就为Excel 插入动态日期和时间。操作步骤第一步：新建一个Excel 工作簿单击 Sheet1 工作表工具宏 Visual Basic编辑器。第二步：在 Sheet1 工作表上右击插入模块。第三步：在模块对话框中输入如下代码：Sub biao()ActiveSheet.Range(a1).Value=Time Application.OnTime Time+TimeSerial(0,0,1),biao End Sub 注：上述代码中的 a1 是表示

2、我们要在 A1单元格中建立一个电子时钟，如果想在其他单元格中建立一个电子时钟，可以把A1改成相应的单元格地址。第四步：关闭 Visual Basic编辑器，返回到 Excel 工作簿中工具宏宏(M),。第五步：在宏对话框中选择biao 执行。快看一看吧！怎么样一个漂亮的电子时钟就出来了，它的秒数还在动呢？怎么样是不是很心动，看完本文尽快试一试吧（如图）！注意：刚建入的时钟的字号比较小，由于电子时钟占用的单元格空间较宽，所以大家要先把单元格的宽度调整一下才能看到电子时钟。电子时钟的字号、字体、字色、填充背景都可以更改。当文件下一次打开时电子时钟没不能自动变时，需要按一下Alt+F8，单击一下执行

3、就可以了。Excel 表格自动隔行着色：用格式和函数实现我们在 Excel 中浏览一个非常大的工作簿中的数据时，有时会出现看错行的现象发生。如果能隔行填充上不同的颜色，这种现象就应该不会发生了。我们利用条件格式和函数来实现这一需求，来进行Excel 表格自动隔行着色操作。1、启动 Excel，打开相应的工作簿文档。2、选中数据区域(或用 Ctrl+A 组合键选中整个工作表)。3、执行“格式条件格式”命令，打开“条件格式”对话框(如图 1)。-2-图 1 条件格式对话框4、单击“条件 1”下面左侧方框右边的下拉按钮，在随后出现的下拉列表中，选择“公式”选项，并在右侧的方框中输入公式：=MOD(R

4、OW(),2)=0。5、单击其中的“格式”按钮，打开“单元格格式”对话框(如图 2)。图 2 单元格格式6、切换到“图案”标签下，选中一种颜色，确定返回到“条件格式”对话框，按下“确定”按钮退出。7、隔行填充不同颜色的效果得以实现(如图 3)。-3-图 3 实现的效果在 Excel 中实现金额数据的分列功能在 Excel 中实现金额数据的分列功能，财会人员在登记日记帐或支票填写等工作中，经常需要将金额数据分列填写在对应的格子。如果使用 Excel 处理这些报表，普通的用户可能会一个数字一个数字进行填写，一不小心可能会出错，因此财会人员 Excel 能自动实现金额数据分列的功能以便实现快速输入，

5、经过广大Excel 高手多年的共同努力，此技术已经可以通过函数嵌套来实现。第 1 步 先绘制类似日记帐表格，如图1 所示。第 2 步 在 A2单元格中输入具有代表性的测试数据，如图2 所示。-4-第3步在B2单元格中输入公式=MID(REPT(,COLUMNS($B:$L)-LEN($A2*100)&$A2*100,COLUMN(A:A),1)，如图 3 所示。第 4 步 拖动 B2单元格的填充柄至L2 单元格，目的是将 B2单元格中的分式复制到后面的单元格中，如图 4所示。-5-第 5 步 先选中 B2:L2 单元格区域，拖动 L2 单元格的填充柄到下面有关的单元格区域，如图 5 所示。第

6、6 步 在 A列输入具有代表性的数字，即可实现金额数据的快速分列，如图6 所示。-6-说明：此公式是通过多个函数的嵌套实现，每个函数的功能我也没有说明，让有兴趣的用户去理解。关键是公式功能的强大，如果你实在不理解此公式的功能，可以将公式直接复制到你需要的单元格，再改变单元格引用即可。Excel 常用技巧整理放送1、两列数据查找相同值对应的位置=MATCH(B1,A:A，0)2、已知公式得结果定义名称=EVALUATE(Sheet1!C1)已知结果得公式定义名称=GET.CELL(6,Sheet1!C1)3、强制换行用 Alt+Enter 4、超过 15 位数字输入这个问题问的人太多了，也收起来

7、吧。一、单元格设置为文本；二、在输入数字前先输入。5、如果隐藏了 B列，如果让它显示出来？选中 A到 C列，点击右键，取消隐藏。选中 A到 C列，双击选中任一列宽线或改变任一列宽。将鼠标移到到 AC列之间，等鼠标变为双竖线时拖动之。6、EXCEL 中行列互换复制，选择性粘贴，选中转置，确定即可。7、Excel 是怎么加密的（1）、保存时可以的另存为右上角的 工具常规设置（2）、工具 选项安全性8、关于 COUNTIF COUNTIF 函数只能有一个条件，如大于90，为=COUNTIF(A1:A10,=90)介于 80 与 90 之间需用减，为 =COUNTIF(A1:A10,80)-COUNT

8、IF(A1:A10,90)9、根据身份证号提取出生日期-7-（1）、=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2),错误身份证号)（2）、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),#-00-00)*1 10、想在 SHEET2 中完全引用 SHEET1 输入的数据工作组，按住 Shift或 Ctrl键，同时选定 Sheet1、Sheet2。11、一列中不输入重复数字 数据-有效性-自定义-公式

9、 输入=COUNTIF(A:A,A1)=1 如果要查找重复输入的数字条件格式公式=COUNTIF(A:A,A5)1 格式选红色12、直接打开一个电子表格文件的时候打不开“文件夹选项”-“文件类型”中找到.XLS 文件，并在“高级”中确认是否有参数1%,如果没有，请手工加上。13、excel下拉菜单的实现 数据-有效性-序列 14、10 列数据合计成一列=SUM(OFFSET($A$1,(ROW()-2)*10+1,10,1)15、查找数据公式两个(基本查找函数为 VLOOKUP,MATCH)（1）、根据符合行列两个条件查找对应结果=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1

10、，0)，FALSE)（2）、根据符合两列数据查找对应结果(为数组公式)=INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0)16、如何隐藏单元格中的0 单元格格式自定义0;-0;或 选项视图零值去勾。呵呵，如果用公式就要看情况了。17、多个工作表的单元格合并计算=Sheet1!d4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4)18、获得工作表名称（1）、定义名称：Name=GET.DOCUMENT(88)（2）、定义名称：Path=GET.DOCUMENT(2)（3）、在 A1中输入=CelL(filename)得到路径级文

11、件名在需要得到文件名的单元格输入=MID(A1,finD(*,SUBST ITUTE(A1,*,LEN(A1)-LEN(SUBSTITUTE(A1,)+1,LEN(A1)（4）、自定义函数Public Function name()Dim filename As String filename=ActiveWorkbook.name name=filename End Function 19、如何获取一个月的最大天数:=DAY(DATE(2002,3,1)-1)或=DAY(B1-1),B1 为2001-03-01 数据区包含某一字符的项的总和,该用什么公式=sumif(a:a,*&某一字符&*

12、,数据区)-8-最后一行为文本：=offset($b$1,MATCH(CHAR(65535),b:b)-1,)最后一行为数字：=offset($b$1,MATCH(9.9999E+307,b:b)-1,)或者：=lookup(2,1/(b1:b1000),b1:b1000)评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分?同时显示出被去掉的分数。看看 trimmean()函数帮助。被去掉的分数：最大两个：=large(data,1;2)最小两个：=small(data,1;2)怎样很简单的判断最后一位是字母right(a1)*1 出错的字母=IF(ISNUMBER(-RI

13、GHT(A1,1),数字,字母)=IF(ISERR(RIGHT(A1)*1),字母,数字)如何 设置单元格，令其不接受包含空格的字符选定 A列数据有效性自定义公式=iserror(find(,a1)数据-有效性-自定义-公式=len(a1)=len(trim(a1)原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22.现在是=sum(n(offset(a1,(row(1:10)-1)*3,)在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据我输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用？=INDIRECT(A1&!&E1)A1为工作表

14、名奇数行求和 =SUMPRODUCT(A1:A1000)*MOD(ROW(A1:A1000),2)偶数行求和 =SUMPRODUCT(A1:A1000)*NOT(MOD(ROW(A1:A1000),2)查看字符串字数=LEN(A1)求非空单元格数量公式计算出来的数据，COUNTA 不能用的(否则空字符也计算进去了)=COUNTIF($E$3:$E$65536,?*)动态求和公式，自A列 A1单元格到当前行前面一行的单元格求和。=SUM(INDIRECT(A1:A&ROW()-1)20、比较好用的 EXCEL 文档修复工具ExcelRecovery 21、EXCEL 开方运算将 8 开 3 次方

15、，可以用这个公式，在单元格中输入=8(1/3)22、单元格中的数据分散对齐文本格式全角输入23、查找工作表中的链接Ctrl+或编辑链接24、如何让空单元格自动填为0-9-选中需更改的区域查找空替换0 25、把 Word 里的数字转换到 Excel方法有多种，选中复制设置输入单元格为文本选择性粘贴值。选中表格转换为文本粘贴分列对分列选项设置为文本。另存为文本文件 EXCEL 中打开文本文件对导入文本对话框进行对应设置。用 Excel 实现按姓氏笔画的排序用 Excel 实现按姓氏笔画的排序，除了能用word 实现姓氏笔画的排序之外，其实作为表格处理高手的 Excel 也可以实现这一功能。首先单击

16、表格中任意一个单元格。然后单击“数据-排序”，在弹出的“排序”对话框中点击“选项”按钮，在“方法”中选择“笔画排序”。确定后，回到“排序”对话框，在“主要关键字”一栏中选择要排序的列名。然后再从右边按需选择“升序”或是“降序”，点击“确定”按钮，数据就会自动按笔画排列了。Excel 数据表格的分分合合作为 Office中人，经常需要处理一些分分合合的数据，若没有几下高招，有人恐怕一个回合就要-10-缴械投降。一、数据的拆分与提取学员小刘是一名业绩非常出色的销售经理，他在记录客户的通讯地址和邮政编码时，将它们放在了一个单元格中（如图1），到了年底想利用Word中的“邮件合并”功能给每位客户发送贺

17、卡邮件时，看着三四千条客户信息，才发现这样的记录方式有问题，赶紧发邮件向我求助。幸运的是，这个Excel 文档中填写地址和邮政编码数据的中间有一个空格，所以可以在10 秒钟之内轻松搞定。（1）数据拆分操作由于数据量较大，所以要先通过列号将整列全部选中。进入“数据分列”命令，选择默认的“分隔符号”选项，单击“下一步”按钮。然后将中间“空格”的选项选中，会在下方看到分列的预览。再进入“下一步”（见图 2），单击“完成”按钮，好了，大功告成。-11-如果中间没空格，前面的客户地址信息文本长度又不一致呢？也不难，15 秒即可完成。无法使用“分列”命令，就使用Excel 的文本提取函数。在Excel 中

18、文本提取函数常用的有3 种：“LEFT”、“RIGHT”和“MID”，可以分别从“左”、“右”和“中间”提取单元格中的文本字符。本例中，分别使用“LEFT”和“RIGHT”来提取客户地址信息和邮政编码。（2）文本提取函数的操作先来看看右侧“邮政编码”数据的提取。将光标定在B2单元格中，输入公式“=RIGHT（A2，6）”，回车后，就能自动提取出数据源A2 单元格右侧第 6 位文本字符，也就是邮政编码数据信息，最后填充柄填充就能计算出下方地址中所有右侧的6位邮政编码信息（见图3）。面再来看看左侧“客户地址”数据的提取。将光标定在第1 个客户地址单元格后面C2单元格中，然后在单元格中输入公式“=L

19、EFT（A2，LEN（A2）-6）”。回车，再用填充柄向下填充所有客户地址信息（见图 4）。小提示：由于左侧的文本字符长度不一致，所以要在“LEFT函数”的第 2 个参数中嵌套一个“LEN函数”。“LEN函数”的作用是计算出A2单元格的总字符个数，然后用总的字符数减去6 位邮政编码，计算出地址的文本字符数。二、数据的合并除了把数据进行拆分或提取，数据常常需要合并，有两种常用方法。下面来看一个例子，客户所在的“城市”和“地址”信息分别记录在了两个单元格中，现在需要将它们合并到一起，生成一个“通讯地址”，如何实现？将光标定在 C2 单元格中，然后在其中输入公式“=A2&市&B2”（这个公式说明有3

20、 个文本相加，分别是城市名称、“市”字符和客户地址信息，见图5）。回车后，销售城市地区和客户地址信息就合并到了一起，最后用填充柄向下填充。如果使用“CONCATENATE函数”，在 B2单元格中输入“=CONCATENATE（A2，市，B2）”，也可以把多个文本进行合并，大家可根据个人喜好自由选择。-12-名师点睛：很多公司或人员都把Excel 当作小型数据库来使用，数据查询工作必不可少。这些查询功能有一个相同的前提，就是尽可能地将数据按照不同的内容分类，把数据拆分成信息单一的“字段”表的形式。例如，为通讯地址信息分别建立“城市”和“地址”2 个字段，为电话信息分别建立区号”、“总机”和“分机

21、”3 个字段。这样才能按照字段进行排序、筛选、数据透视表、甚至使用一些查找函数来查询和检索数据。在使用或查看数据信息时，有时又需要较完整的数据信息，那么就需要将数据进行合并。无论拆分还合并只是一种操作手段，目的是利用Excel 将数据信息管理好，让它为我们的应用提供便利，成为我们办公中的得力助手。Excel 常用公式汇总1.总分函数公式为：=SUM(A2：A70)2.平均分函数公式为：=AVERAGE（A2：A70）3.分数段（96 分以上）函数公式：=COUNTIF(A2：A70,=96)4.分数段（80 分89 分）函数公式：=COUNTIF(A2：A70,=80)-COUNTIF(A2：

22、A70,=90)5.最高分函数公式为：=MAX（A2：A70）6.最低分函数公式为：=MIN（A2：A70）7.排名函数公式为：=RANK(F2,F$2：F$70)8.班级平均分公式=IF(COUNTIF($C$2:$C$24,J26)=0,SUMIF($C$2:$C$24,J26,$D$2)/COUNTIF($C$2:$C$24,J26)9.计算 90分以上的平均绩:=ROUND(AVERAGE(IF(ISNUMBER(B2:B10)*(B2:B1090),B2:B10),2)10.计算平均成绩，成绩空白也计算:=AVERAGEA(B2:B11*1)11.COUNTIF 函数只能有一个条件，

23、如大于90，为=COUNTIF(A1:A10,=90)12.介于 80与 90之间需用减，为 =COUNTIF(A1:A10,80)-COUNTIF(A1:A10,90)13.班级平均分公式=IF(COUNTIF($C$2:$C$24,J26)=0,SUMIF($C$2:$C$24,J26,$D$2)/COUNTIF($C$2:$C$24,J26)-13-14.如何用公式求出最大值所在的行？如 A1：A10中有 10 个数，怎么求出最大的数在哪个单元格？=MATCH(LARGE(A1:A10,1),A1:A10,0)=ADDRESS(MATCH(SMALL(A1:A10,COUNTA(A1:A

24、10),A1:A10,0),1)=ADDRESS(MATCH(MAX(A1:A10,1),A1:A10,0),1)15.排名次的两种方法：假定 E列为成绩，F 列为名次，F2单元格公式如下：=RANK(E2,E:E)这种方法，分数相同时名次相同，随后的名次将空缺。例如：两个人 99分，并列第 2 名，则第 3 名空缺，接下来是第4 名。用公式排序(中国式排名)：假定成绩在 E列,请在 F2输入公式：=SUM(IF(E$2:E$1000E2,1/COUNTIF(E$2:E$1000,E$2:E$1000)+1 公式以 Ctrl+Shift+Enter三键结束。第二种方法分数相同的名次也相同，不过

25、随后的名次不会空缺。16.计算 B3：C6和 C3：E6这两组区域的值，可以用以下公式：“=Sumproduct(B3:C6,D3:E6)”。17.在 A1、B1单元格中分别输入120、90，那么如果要求 A1与 B1之间的差的绝对值，可以在C1单元格中输入以下公式：“=ABS(A1-B1)”。18.计算 B3：D3这一区域中 3个数字的最大公约数，可以在 E3单元格中输入以下公式：“=GCD(B3,C3,D3)”。19.计算 B3：D3这一区域中 3个数字的最小公倍数，可以在 E3单元格中输入以下公式：“=LCM(B3,C3,D3)”。20.理解 IF 函数在计算个人所得税中的用法设工资表中

26、“应纳税所得额”在E列，“应交个人所得税”在H列。自动套用适用税率和速算扣除数计算应纳税额。IF(E2 100000，E2*0.45-15375，IF（E2 80000，E2*0.40-10375，IF(E2 60000，E2*0.35-6375，IF(E2 40000，E2*0.30-3375，IF(E2 20000，E2*0.25-1375，IF(E2 5000，E2*0.20-375，IF(E2 2000，E2*0.15-125，IF(E2 500，E2*0.10-25，E2*0.05)该函数十分亢长，其中嵌套了7 个同样的 IF 函数，从第 2 个 IF 函数开始到最后是第1 个嵌套函

27、数，从第 3 个 IF 开始到最后是第2 个嵌套函数,，为帮助理解，我们将这些嵌套函数分别设为X1、X2、,于是将整个函数简化如下：IF（E2 100000，E2*0.45-15375，X1）该函数意为：当 E2中工资额大于 100000元时，H2中计算出的应纳所得税额为E2*45%-15375，否则（指当工资额等于或小于100000元时），则 H2应按 X1的方法计算；把X1展开：-14-IF（E2 80000，E2*0.40-10375，X2）其含义与上面相仿。最后一个嵌套函数X7展开为：IF（E2 500，E2*0.10-25，E2*0.05）该函数意为：当 E2中工资额大于 500元时

28、，H2中计算出的应纳所得税额为E2*10%-25，否则（当工资额等于或小于 500元时），H2等于 E2*5%。21.用特殊符号补齐位数和财务打过交道的人都知道，在账面填充时有一种约定俗成的“安全填写法”，那就是将金额中的空位补齐，或者在款项数据的前面加上“$”之类的符号。其实，在Excel 中也有类似的输入方法，那就是“REPT”函数。它的基本格式是“=REPT(“特殊符号”，填充位数)”。比如，我们要在中A2单元格里的数字结尾处用“#”号填充至 16 位，就只须将公式改为“=(A2&REPT(#,16-LEN(A2)”即可；如果我们要将A3单元格中的数字从左侧用“#”号填充至 16 位，就

29、要改为“=REPT(#,16-LEN(A3)&A3”；另外，如果我们想用“#”号将 A4中的数值从两侧填充，则需要改为“=REPT(#,8-LEN(A4)/2)&A4&REPT(#)8-LEN(A4)/2)”；如果你还嫌不够专业，要在A5单元格数字的顶头加上“$”符号的话，那就改为：“=(TEXT(A5,$#,#0.00(&REPT(#,16-LEN(TEXT(A5,$#,#0.00)”，一定能满足你的要求。22.3 步搞定在 Excel 中“竖排文字”的方法在 word 中我们知道文字可以横排，也可以竖排，但是在 execl 中我们很多老师在制作excel 表格时却找不到“竖排文字”的按钮或

30、命令，往往采取换行或其它的替代办法。其实execl 和 word 一样可以竖排文字的。调出“竖排文字”按钮的方法如下：1、选择“视图”-“工具栏”菜单下的“自定义”选项，出现“自定义”对话框。选中“命令”选项卡，在左边的“类别”列表框中选择“格式”选项，在右边的“命令”列表框里，就会看到“竖排文字”按钮了(如图 1)。2、把竖排文字按钮拖到工具栏上在工具栏上就有了一个名为“竖排文字”的按钮了3、选中已输入文字的单元格，单击“竖排文字”按钮，单元格中的所有文字全部被竖排了。23.实验公式1.例如：A1大于 300 万少于 500 万时，超出 300 万部分按 10%计算；500-800 万时，超

31、出 500 万部分按11%计算；800 万以上部分按 20%计算。=MAX(A1-300,500,800)*10%,11%,20%+0,20,53,0)-15-进一步简化为=MAX(A1*0.1,0.11,0.2-30,35,107,0)当然这是以万为单位的，如果A1不以万为单位，公式可改为=MAX(A1*0.1,0.11,0.2-30,35,107*10000,0)如果要用 IF 公式，可以改为=IF(A1=300,IF(A1=500,(A1-300)*10%,IF(A1=800,(A1-500)*11%+20,(A1-800)*20%+53)可进一步简化为=IF(A1=300,IF(A1=

32、500,A1*10%-30,IF(A1=800,A1*11%-35,A1*20%-107)24.个人收入调节税计算假设个人收入调节税的稽征办法是：工资在1000元以下的免征调节税，工资1000元以上至 1500元的超过部分按 5%的税率征收，1500元以上至 2000元的超过部分按8%的税率征收，高于2000 元的超过部分按 30%的税率征收。假如工作表的 B列存放职工姓名，C列存放工资，选中 D列的空白单元格输入公式“=IF（C2=1000，IF（C2-1000）=1500，（C2-1000）*0.05，IF（C2-E22000，（C2-2000）*0.3）”，回车后即可计算出C2工资应缴纳

33、的收入调节税。25.计算个人调节税的自定义函数，任务假设个人调节税的收缴标准是：工资小于等于800元的免征调节税，工资800 元以上至 1500元的超过部分按 5的税率征收，1500元以上至 2000元的超过部分按 8的税率征收，高于2000 元的超过部分按 20的税率征收。分析假设 Sheet1 工作表的 A、B、C、D列中分别存放“姓名”、“总工资”、“调节税”、“税后工资”字段数据，如图 1 所示。图 1 平时使用较多的方法是借助嵌套使用IF 函数计算，比如在C2单元格输入公式“=IF(B2=800,0,IF(B2=1500,(B2-800)*0.05,IF(B2=2000,700*0.

34、05+(B2-1500)*0.08,700*0.05+500*0.08+(B2-2000)*0.2)”，然后通过填充柄复制公式到C列的其余单元格。-16-26.计算个人所得税:=ROUND(0.05*SUM(H2-1600-0,500,2000,5000,20000,40000,60000,80000,100000+ABS(H2-1600-0,500,2000,5000,20000,40000,60000,80000,100000)/2,0)27.个税计算公式中的速算扣除数是什么意思？个人所得税起征点为2000元,即超两千的部分就要交税。超出部分要看金额的大小来按等级交纳，为了方便快速的计算，

35、就有了一个速算扣除数，理论说来太烦琐，以案列来说，如后图就是等级表格，不超过 500 元的没有速算扣除数，超过的就有相应的速算扣除数，如第二级、第三级相应速算扣除数为25、125。假如你工资为 3278元，扣除 2000元，即你工资超出起征点1278元，看下图表，对应图中的第二级别（500 元-2000 元），那你的税率为10%，速算扣除数为 25 元，即你应交纳个人所得税=（3278-2000）*10%-25=102.8 元。像我工资 2400元,比起征点(2000 元)高出 400 元,看下图可知对应第一等级,税率 5%,无速算扣除数,我应交税=(2400-2000)*5%=20 元.个人

36、所得税税率表级数含税级距税率(%)速算扣除数1 不超过 500元的 5 0 2 超过 500元至 2,000 元的部分 10 25 3 超过 2,000 元至 5,000 元的部分 15 125 4 超过 5,000 元至 20,000 元的部分 20 375 5 超过 20,000 元至 40,000 元的部分 25 1,375 6 超过 40,000 元至 60,000 元的部分 30 3,375 7 超过 60,000 元至 80,000 元的部分 35 6,375 8 超过 80,000 元至 100,000 元的部分 40 10,375 9 超过 100,000 元的部分 45 15

37、,375 28.有出生日期，准确计算一个人的年龄（周岁）？=DATEDIF(A1,NOW(),y)29.excel 下拉菜单的实现 数据-有效性-序列 30.强制换行用 Alt+Enter-17-31.根据目前的居民生活用电按3 个梯度收费：月用电量150千瓦时及以下部分，每千瓦时0.4463 元，151400 千瓦时部分为 0.4663 元，401 千瓦时以上部分为0.5663 元；合表用户不分梯度，统一价格为每千瓦时 0.4643 元。比如，一个家庭的用月电量在830千瓦时，那就要怎么计算电费阶梯 1电费 830*0.4463 370.429 阶梯 2电费（400*2-150*2）*0.0

38、2 10 阶梯 3电费（830-400*2）*0.12 3.6 总电量为：370.429+10+3.6=384.029 元说明：按月抄见电量实行阶梯式累进加价：用电量低于150 千瓦时部分电价为每千瓦时0.4463 元，151-400 千瓦时部分每千瓦时加价0.02 元；超过 400 千瓦时部分每千瓦时加价0.12 元。隔月抄表按阶梯基数乘二.32.excel 多样格式公式sheet1 的 A2值等于 sheet2 的 A1 sheet1 的 A6值等于 sheet2 的 A2 sheet1 的 A10值等于 sheet2 的 A3.如此类推。如何在 sheet1 设置公式，可以通过批量复制，

39、求出对应的数值。sheet1 的 A2值等于 sheet2 的 A1(=sheet2!$A$1)sheet1 的 A6值等于 sheet2 的 A2(=sheet2!$A$2)sheet1 的 A10值等于 sheet2 的 A3(=sheet2!$A$3)33.不连续单元格填充同一数据选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。34.excel 中的 sum函数从 A1开始，每隔两行求和（就是A1 A4 A7 A10,A300），一直加到 300，但是这个参数个

40、数超过了30，如何用简单的写法，表明是从 A1开始，每隔两行求和呢？（其实是可以写成“=sum(A1 A4 A7 A10,A300)“,但我觉得这样不是最好的方法。）在单元格输入公式：=SUMPRODUCT(MOD(ROW(a1:a300),3)=1)*a1:a300)，就是你想要的结果了。这个结果是不对的，原因就是不是2 的余数，因该是 3。可以测试其他=SUMPRODUCT(MOD(ROW(a1:a300),2)=1)*(a1:a300)我的方式是在前面新建一列，在A1中输入,=if(mod(row(B1)-1),3)=0,True)，一直拉到 A300 然后在 B列最后输入，=sumif

41、(A1:A300,True,B1:B300)-18-35.用 Excel 做多页的表格时，怎样像Word的表格那样做一个标题，即每页的第一行（或几行）是一样的。但是不是用页眉来完成？在 EXCEL 的文件菜单页面设置工作表打印标题；可进行顶端或左端标题设置，通过按下折叠对话框按钮后，用鼠标划定范围即可。这样Excel 就会自动在各页上加上你划定的部分作为表头。36.行列转置假如 A1-A10单元格中有不同的内容，现在需要将这些内容分别排列在A1、B1、C1、D1.J1单元格中，即将原来在同一列中的内容重新排列在一行中。难道要一个一个单元格去复制粘贴吗？完全不用。首先，选中 A1-A10单元格，

42、单击右键，在弹出的菜单中选择“复制”命令；接着单击B1单元格，再单击右键，在弹出的菜单中选择“选择性粘贴”命令，弹出对话框，选择其中的复选项“转置”，点击确定。这时你会看到B1、C1.K1单元格中已有了A1-A10单元格中的内容，将A列选 中，在右键菜单中删除，此时行列转换工作就完成了。37.快速实现 Excel、Word表格奇偶行交错着色1.选择要进行着色的区域。2.执行“格式条件格式”，打开“条件格式”对话框。单击打开“条件1”的下拉列表，选择“公式”，在右侧的文本框中输入公式“=MOD(ROW(),2)=1”。再单击“格式”按钮，打开“单元格格式”对话框，指定一种颜色。公式的作用是选出行

43、号为奇数的行，凡符合此公式条件的单元格即采用所指定的颜色格式。3.单击“添加”按钮，添加第二个条件公式“=MOD(ROW(),2)=0”，再按上面的方法指定第二种颜色。即凡选中区域中行号为偶数的行，将以此颜色填充。4.单击“确定”按钮。38.excel 中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比如：A11 时，C1显示红色0A11时，C1显示绿色A1“条件格式”，条件1 设为：公式=A1=1 2、点“格式”-“字体”-“颜色”，点击红色后点“确定”。条件 2 设为：公式=AND(A10,A1“字体”-“颜色”，点击绿色后点“确定”。条件 3 设为：公式=A1“字体”-“颜色”

44、，点击黄色后点“确定”。4、三个条件设定好后，点“确定”即出。39.Excel 表中身份证号码提取出生年月、性别、年龄的使用技巧假如身份证号数据在A1单元格，在 B1单元格中编辑公式=IF(LEN(A1)=15,MID(A1,7,2)&-&MID(A1,9,2)&-&MID(A1,11,2),MID(A1,7,4)&-&MID(A1,11,2)&-&MID(A1,13,2)回车确认即可。如果只要“年-月”格式，公式可以修改为=IF(LEN(A1)=15,MID(A1,7,2)&-&MID(A1,9,2),MID(A1,7,4)&-&MID(A1,11,2)40.这是根据身份证号码（15 位和

45、18 位通用）自动提取性别的公式：说明：公式中的B2是身份证号，根据身份证号码求性别：=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3),2)=0,女,男),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1),2)=0,女,男),身份证错)41.根据身份证号码求年龄：=IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2),if(LEN(B2)=18,2007-VALUE(MID(B2,7,4),身份证错)42.这是根据身份证号码（15 位和 18 位通用）自动提取出生年月的公式：=IF(LEN(B2)=15,CONCA

46、TENATE(19,MID(B2,7,2),.,MID(B2,9,2),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),.,MID(B2,11,2),身份证错)43.根据身份证号码求年龄：=IF(LEN(B2)=15,year(now()-1900-VALUE(MID(B2,7,2),if(LEN(B2)=18,year(now()-VALUE(MID(B2,7,4),身份证错)44.根据身份证号计算性别:=IF(MOD(MID(B2,15,3),2),男,女)将 15 位身份证号码升级为18位:=IF(LEN(B2)=18,B2,LEFT(REPLACE(B2,7

47、,19),17)&MID(10X98765432,MOD(SUM(MID(REPLACE(B2,7,19),ROW(INDIRECT(1:17),1)*2(18-ROW(INDIRECT(1:17),11)+1,1)45.通过身份证号码计算年龄:=TEXT(TODAY(),YYYY)-(IF(LEN(B2)=18,19)&LEFT(REPLACE(B2,1,6,),2+(LEN(B2)=18)*2)46.根据身份证号码统计男性人数:=SUM(MOD(LEFT(RIGHT(B2:B11,1+(LEN(B2:B11)=18),2)-20-根据身份证号码汇总男、女职工总数:男=SUM(-ISODD(

48、MID(B2:B10,15,3);女=SUM(-ISEVEN(MID(B2:B10,15,3)47.年龄计算公式：=IF(YEAR(E2)=2008)*(MONTH(E2)8),0,2008-YEAR(E2)IF(MONTH(E2)=8,0,-1)其中 e2为单元格，2008 为当前年份48.身份证号中提取出生年月=MID(J11,7,4)&年&MID(J11,11,2)&月&MID(J11,13,2)&日 49.相同格式多工作表汇总求和方法假定同一工作薄有SHEET1 至 SHEET100 共 100 个相同格式的工作表需要汇总求和，结果放在SHEET101 工作表中，请在 SHEET101

49、 的 A1单元格输入：=SUM(单击 SHEET1 标签，按住 Shift键并单击 SHEET100 标签，单击 A1单元格，再输入：）此时公式看上去内容如下：=SUM(SHEET1:SHEET100!A1)按回车后公式变为=SUM(SHEET1:SHEET100!A1)所以，最简单快捷的方法就是在SHEET101 的 A1单元格直接输入公式：=SUM(SHEET1:SHEET100!A1)然后按回车。50.填充条纹如果想在工作簿中加入漂亮的横条纹，可以利用对齐方式中的填充功能。先在一单元格内填入“*”或“”等符号，然后单击此单元格，向右拖动鼠标，选中横向若干单元格，单击“格式”菜单，选中“单

50、元格”命令，在弹出的“单元格格式”菜单中，选择“对齐”选项卡，在水平对齐下拉列表中选择“填充”，单击“确定”按钮。51.条件显示我们知道，利用 If 函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入 60以下的分数时，能显示为“不及格”；输入60 以上的分数时，显示为“及格。这样的效果，利用 IF 函数可以很方便地实现。假设成绩在 A2单元格中，判断结果在A3单元格中。那么在A3单元格中输入公式：=if（A260，“不及格”，“及格”）同时，在 IF 函数中还可以嵌套IF 函数或其它函数。例如，如果输入：=if（A260，“不及格”，if（A2=90，“及格”，“