Excel常用公式.doc

Excel常用公式和使用方法1 3步搞定在Excel中“竖排文字”的方法 在word中我们知道文字可以横排，也可以竖排，但是在execl中我们很多老师在制作excel表格时却找不到“竖排文字”的按钮或命令，往往采取换行或其它的替代办法。其实execl和word一样可以竖排文字的。调出“竖排文字”按钮的方法如下：1、选择“视图”-“工具栏”菜单下的“自定义”选项，出现“自定义”对话框。选中“命令”选项卡，在左边的“类别”列表框中选择“格式”选项，在右边的“命令”列表框里，就会看到“竖排文字”按钮了(如图1)。图1：2、把竖排文字按钮拖到工具栏上在工具栏上就有了一个名为“竖排文字”的按钮了 (如图2)。图2：.3、选中已输入文字的单元格，单击“竖排文字”按钮，单元格中的所有文字全部被竖排了。(如图3)图3：2EXCEL表格中能否只显示数值不显示公式1. 不仅是要用户无法修改我们设定的公式，更要用户也无法知晓我们公式的内容。如何做：1、Ctrl+A全选工作表，右键“设置单元格格式”，保护标签，取消锁定和隐藏的选择。（此步骤的目的，是允许用户在保护后的工作表中输入数据）。2、Ctrl+G呼出定位框，（或按F5）单击定位条件，选择公式，确定。（执行此操作使后续步骤只针对公式所在的单元格）3、右键“设置单元格格式”，保护标签，勾选锁定和隐藏。4、选择菜单 工具保护保护工作表，输入密码，确定。现在你选择某个公式单元格，上面的公式输入框将不在显示公式。3excel多样格式公式sheet1的A2值等于sheet2的A1sheet1的A6值等于sheet2的A2sheet1的A10值等于sheet2的A3 。如此类推。如何在sheet1设置公式，可以通过批量复制，求出对应的数值。sheet1的A2值等于sheet2的A1 (=sheet2!$A$1)sheet1的A6值等于sheet2的A2 (=sheet2!$A$2)sheet1的A10值等于sheet2的A3 (=sheet2!$A$3)4EXCEL中排名次的两种方法：（一）、用RANK()函数： 假定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三键结束。第二种方法分数相同的名次也相同，不过随后的名次不会空缺。5多张工作表中输入相同的内容 几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2.来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着出现在选中的其它工作表内。6不连续单元格填充同一数据 选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。7excel中的sum函数从A1开始，每隔两行求和（就是A1 A4 A7 A10 A300），一直加到300，但是这个参数个数超过了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(A1:A300,True,B1:B300)8用Excel做多页的表格时，怎样像Word的表格那样做一个标题，即每页的第一行（或几行）是一样的。但是不是用页眉来完成？ 在EXCEL的文件菜单页面设置工作表打印标题；可进行顶端或左端标题设置，通过按下折叠对话框按钮后，用鼠标划定范围即可。这样Excel就会自动在各页上加上你划定的部分作为表头。 9行列转置 假如A1-A10单元格中有不同的内容，现在需要将这些内容分别排列在A1、B1、C1、D1.J1单元格中，即将原来在同一列中的内容重新排列在一行中。难道要一个一个单元格去复制粘贴吗？完全不用。 首先，选中A1-A10单元格，单击右键，在弹出的菜单中选择“复制”命令；接着单击B1单元格，再单击右键，在弹出的菜单中选择“选择性粘 贴”命令，弹出对话框，选择其中的复选项“转置”，点击确定。这时你会看到B1、C1.K1单元格中已有了A1-A10单元格中的内容，将A列选 中，在右键菜单中删除，此时行列转换工作就完成了。10.个人收入调节税计算假设个人收入调节税的稽征办法是：工资在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工资应缴纳的收入调节税。公式中的IF语句是逐次计算的，如果第一个逻辑判断C2=1000成立，则公式所在单元格被填入空格；如果第一个逻辑判断式不成立，则计算第二个IF语句；直至计算结束。如果税率征收标准发生了变化，只须改变逻辑和计算式中的值，如1000、1500和2000等即可。11.快速实现Excel、Word表格奇偶行交错着色1.选择要进行着色的区域。 2.执行“格式条件格式”，打开“条件格式”对话框。单击打开“条件1”的下拉列表，选择“公式”，在右侧的文本框中输入公式“=MOD(ROW(),2)=1”。再单击“格式”按钮，打开“单元格格式”对话框，指定一种颜色。公式的作用是选出行号为奇数的行，凡符合此公式条件的单元格即采用所指定的颜色格式。 3.单击“添加”按钮，添加第二个条件公式“=MOD(ROW(),2)=0”，再按上面的方法指定第二种颜色。即凡选中区域中行号为偶数的行，将以此颜色填充。 4.单击“确定”按钮，关闭对话框，选中区域即遵照上述设置按行号的奇偶数交错着色。 用这种方法制作出的奇偶行颜色交错的表格，有一个特点：在此区域中添加删除行时，其他所有受影响行都能根据其行号的奇偶自动重新调整颜色。具体效果请看下面的动画。 12.企业收入调节税计算例如：A1大于300万少于500万时，超出300万部分按10%计算；500-800万时，超出500万部分按11%计算；800万以上部分按20%计算。借助一下个人所得税计算公式=MAX(A1-300,500,800)*10%,11%,20%+0,20,53,0)进一步简化为=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=500,A1*10%-30,IF(A1=800,A1*11%-35,A1*20%-107)13.多张工作表中输入相同的内容 几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2.来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着出现在选中的其它工作表内。 14.不连续单元格填充同一数据 选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据15.四舍五入函数应用实例：消除计算误差假设Excel工作表中有D2=356.68、E2=128.12，需要将D2与E2之和乘以0.1，将计算结果四舍五入取整数，再将这个结果乘以1.36（取两位小数）得到最终结果。一般用户的做法是选中某个单元格（如F2），使用“单元格”命令将它的小数位数设为零，然后在其中输入公式“F2=（D1+E1）*0.1”。再将G2单元格的小数位数设成两位，最后把F2*1.36的结果存入其中就可以了。从表面上看，上述方法没有什么问题。因为（D1+E1）*0.1在F2单元格显示48（注意：是显示48），如果F2单元格的小数位数为零，（D1+E1）*0.1经四舍五入后的结果就是48。接下去却出了问题，因为F2*1.36的计算结果是65.90，根本不是48*1.36的正确结果65.28，其中65.90是（D2+E2）*0.1未经四舍五入直接乘以1.36的结果。以上计算结果说明：“单元格格式”、“数字”选项卡设置的“小数位数”，只能将单元格数值的显示结果进行四舍五入，并不能对所存放的数值进行四舍五入。换句话说，单元格数值的显示结果与实际存放结果并不完全一致，如果不注意这个问题，计算工资等敏感数据就会出现错误