不同数据用不同颜色显示.doc

不同数据用不同颜色显示在工资表中，如果想让大于等于2000元的工资总额以“红色”显示，大于等于1500元的工资总额以“蓝色”显示，低于1000元的工资总额以“棕色”显示，其它以“黑色”显示，我们可以这样设置。 1.打开“工资表”工作簿，选中“工资总额”所在列，执行“开始样式条件格式”命令，打开“条件格式”对话框。单击第二个方框右侧的下拉按钮，选中“大于或等于”选项，在后面的方框中输入数值“2000”。单击“格式”按钮，打开“单元格格式”对话框，将“字体”的“颜色”设置为“红色”。如下图2.按“添加”按钮，并仿照上面的操作设置好其它条件(大于等于1500，字体设置为“蓝色”；小于1000，字体设置为“棕色”)。 3.设置完成后，按下“确定”按钮。 看看工资表吧，工资总额的数据是不是按你的要求以不同颜色显示出来了。如何计算个人所得税？【问题】 如何用Excel公式计算个人所得税？ 【解答】公式：=ROUND(MAX(A1-2000)*0.05*1,2,3,4,5,6,7,8,9-25*0,1,5,15,55,135,255,415,615,0),2) A1-2000表示应纳税所得额库龄分析的颜色提示库龄分析是存货管理的一个重要组成部分，准确、直观的库龄分析，为管理工作提供有效的数据支持。【例】在下图1的库龄分析表中，对E2:E8区域作下列设置。 要求： 库龄=30天且=90天的单元格背景色设置为红色。 图1操作步骤 （1）选取E2:E8区域。 （2）打开【条件格式】对话框，分别设置三个单元格数值条件和相应格式，如图2所示，设置结果如图3所示。 图2 图3制作应收账款催款提醒在应收账款管理工作中，经常需要在Excel里设置自动催款提醒，根据实际情况设置要催缴欠款的客户。【例】如图1所示，在应收账款明细表中设置催缴条件。具体要求：根据C列“约定还款日期”和D列“是否还款”判断。如果超过还款日期10天还未还款（假设今天的日期为2005-11-24），则填充所在行为红色背景。 图1 操作步骤 （1）从A3单元格起，选取整个表格区域，这里要说明的是，一般情况下都要向下多选些空白区域。因为将来的应收账款明细表数据可能还要继续增加，新增加的也要应用同样的提醒，另外和以前不同的是，这里是选定整个区域，原因是对符合条件的单元格所在行整行填充颜色。 （2）打开【条件格式】对话框，选择【突出显示规则】选项，在选择【其他规则】，在后面的文本框中输入公式：“=AND(TODAY()-$C3)10,$D3=否)”，设置背景色为红色，如图2所示。 图2公式说明： AND：表示括号中的两个条件要同时满足。 TODAY()-$C310：TODAY()返回的是当天的日期，也就是系统日期。$C3是第一个单元格的日期，这里使用行相对列绝对（$C3），是因为这个公式要适用于数据表所有选定的单元格。Excel在设置条件格式时同复制公式一样，有智能变换单元格的优点，比如A1的公式为“=B1*10”，当公式复制到A2时，公式会自动转换为“=B2*10”，这种变换是受$（绝对引用符号）控制的。本例中无论在任意一行的任意一个单元格，都要根据C列和D列的内容进行判断，所以要对列进行绝对引用。 $D3=否：这是第二个条件，判断欠款户是否已还款，如果单元格内容是否，则表示欠款户还未还款，符合条件。 （3）如图3所示，符合条件的行全部填充为红色背景。 图3 如何监视重复录入？在录入内容时，有时要避免重复录入，如果边输入边查找已经录入的是否有重复，这样会非常麻烦。数据有效性恰好能解决这个问题。如果是对已录入完毕的数据表寻找重复录入的内容，可以用条件格式来设置。【例】 如图1所示，在用户档案中，如果名子重复且地址重复，则视为重复录入，并填充红色背景。 图1操作步骤 （1）选取A:C列（从A列选起），输入公式： “=AND(COUNTIF($A:$A,$A1)=2,COUNTIF($B:$B,$B1)=2)”，如图2所示。 图2（2）设置背景色为红色，结果如图3所示。 图3如何格式化账簿？在财务日常工作中，设置账簿是一项重要内容，设置一个标准、美观的账簿也是一件很有趣的事情。【例】在使用Excel做账簿时，需要完成下列设置。 隔行填充淡黄色背景。 本月小计和本月累计行填充灰色字体并设置双下划线。 本月小计和本月累计行背景设置为红色。 操作步骤 （1）选取要设置格式的区域。 （2）设置第一个公式条件和格式。公式：“=OR($A3=本日小计,$A3=本月累计)”；格式设置：背景浅灰色，字为红色且加双下划线。 （3）设置第二个公式条件和格式。公式：“=MOD(ROW(),2)=0”；格式设置：背景为浅黄色。（4）完成全部设置结果如图所示。 隐藏公式中的错误值由于各种原因，在单元格中会产生一些错误值符号，这些符号在打印时会影响打印效果。下面介绍一个隐藏错误值符号的有效方法。【例】 把单元格中所有错误值隐藏，如图1所示。 图1操作步骤 （1）选取单元格区域D2:D7，设置条件公式：“=ISERROR(D2)”。 （2）设置单元格颜色为背景色，最终结果如图2所示。 图2 快速提取身份证全部信息【问题】如何通过身份证号码查询籍贯、年龄、性别？【解答】具体方法如下图所示：统计各部门的奖金总数【例】人力资源部到月底需要统计整个公司的奖金分配情况，如下图：我们可以用SUMIF语句来完成这个操作，操作步骤如下图：快速完成HR统计工作进行人力资源统计报表工作时间很繁琐的事情。我们可以借助Excel来帮我们完成人数统计，工资统计，还有一些多条件的统计。这个函数是：SUMPRODUCT()函数。 该函数在EXCEL定义中描述为在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。这种描述给人的感觉似乎是对数组进行计算，对乘积汇总。但实际上它对于多条件求和方面的功能超乎人们的想象，特别是应用于人力资源方面统计更是超强，不仅能完成多条件的统计功能，而且人数统计和工资汇总统计都能实现，灵活应用可以取代COUNTIF()和SUMIF()，因此掌握该这个函数的使用方法，可以说完成任何统计报表的数据统计工作，都能做到游刃有余。 SUMPRODUCT()函数进行多条件计数统计时，如条件是“或者”关系，即同时满足。必须用*号连接判断条件， 其公式形式如下：SUMPRODUCT(条件1*条件2*条件3条件N) ，如下图：邮件合并的应用在做外部人员招聘时候，选择合适的渠道来发布招聘信息。我们会收集到一些简历。按照人力资源管理流程对这些简历的筛选工作紧随其后，初步筛选掉明显不符合任职要求的应聘者。然后约应聘者进行结构化面试，再次筛选不符合任职资格的人员，发出婉拒信 但许多招聘企业不做这个事情，说：“你等3天吧，不给你打电话就是不行了”。做招聘时可能觉得通知应聘者未被公司录用的消息是整个招聘过程中比较棘手的工作。但成熟的企业对于不符合任职资格要求，均会给出“婉拒信”。下面是制作婉拒信的基本步骤： 第一步：建立Excel格式的“未聘用人员档案”以备以后所需，另一方面发出婉拒信快速通知应聘者消息。给这部分人建立档案连同发送婉拒信其实都很简单。Excelwordoutlook 会高效完成这个工作。 第二步：依据应聘者建立excel格式的“应聘者基本信息表”。如下图1：再写一封婉拒信，如图2：图1图2第三步：依旧打开“通知书”word文档 ，工具栏空白处单击右键，弹出菜单，将“邮件合并”勾选，这样邮件合并工具栏就会出现了。我们整个工作都是需要这个工具栏来帮忙的。如图3：图3（1）打开数据源： 使word文档与excel建立关系，点击后弹出对话框，选择刚刚建立的“应聘人员基本信息表”作为数据源，选择表sheet1。图4图5（2）在word中插入“域”，点击“插入域”按钮，逐条插入匹配的域到到word中。如图6：图6下一步弹出对话框，如图7： 图7这个实例只需插入2个域就可以了，一个是姓名一个是应聘岗位。 （3）最后再查看一下文档是否有错误，无误后，点击“合并到电子邮件”按钮，弹出对话框，在收件人栏目中选择“email”，这就是发邮件时候用到的email地址。图8按下确定按钮后，我们会发现word的任务栏在闪烁，这个时候word其实在后台紧张的工作，他把邮件“打包”到了outlook那里去了，稍等一会儿我们打开outlook，查看“发件箱”。几个待发“通知书”已经在那里了。我们的工作至此已经完成99了，所需的只是最后一步发出邮件了，最后这最重要的1由你完成吧。 另外，如果有需要打印邮件（纸张邮寄）的时候，我们在“第三步”时候选择邮件合并工具栏的“合并到新文档”按钮就可以了，其他步骤相同。效果如图9：图9据入职日期计算入职年龄【问题】我公司要验厂,领导要求在一花名册中根据员工入职日期计算并找出满16周岁但没满18周岁的未成年工。如下图所示：【解答】如下图可以在D2中输入公式：=DATEDIF(-TEXT(MID(B2,7,(LEN(B2)=18)*2+6),0000-00-00),-TEXT(C2,0000-00-00),y)，然后选定D2:D6,单击【条件格式】【突出显示单元格规则】【介于】，设定1618用红色填充。根据工资标准生成工资表【问题】公司根据不同部门的不同职位制定了一个工资标准，有没有快速的方法能根据职工的基本情况自动生成工资总表？如图所示资料：【解答】在同一个工作簿中建立新表“工资总表”，在B2单元格输入公式：=SUMPRODUCT(基本薪金标准!$A$5:$A$9=VLOOKUP(A3,职工基本情况!$B$3:$D$7,2,0)*(基本薪金标准!$B$5:$B$9=VLOOKUP(A3,职工基本情况!$B$3:$D$7,3,0)*基本薪金标准!$C$5:$C$9)，如下图所示：VLOOKUP函数应用（一）【例】如下两个工作簿，HR-1和HR-2，其中，HR-1存放应聘人员的姓名和电话等资料。HR-2中只存放应聘人员的姓名，现在要求把员工的相关信息从HR-1中导入到HR-2中去。HR-2中有应聘人员的信息在HR-1中不能查到。 在编写代码的时候要注意如图所示： 正确代码为：VLOOKUP函数应用（二）【例】如下两个工作簿，HR-1和HR-2，其中，HR-1存放应聘人员的姓名和电话等资料。HR-2中只存放应聘人员的姓名，现在要求把员工的相关信息从HR-1中导入到HR-2中去。HR-2中有应聘人员的信息在HR-1中不能查到，现在要求屏蔽错误值，即当VLOOKUP查找不到相应的数值时，单元格留空。正确的代码如下图： 显示结果为：如何建立工作簿中表的链接？Dim Pt As Range Dim i As Integer With Sheet1 Set Pt = .Range(a1) For i = 2 To ThisWorkbook.Worksheets.Count .Hyperlinks.Add Anchor:=Pt, Address:=, SubAddress:=Worksheets(i).Name & !A1 Set Pt = Pt.Offset(1, 0) Next i End With End Sub如何把Excel单元格变得凹凸有致现在仍然选中这些单元格区域，点击功能区“样式”选项卡中“条件格式”按钮下的小三角形，在弹出的快捷菜单中点击“新建规则”命令， 在打开的“新建格式规则”对话框中，点击“选择规则类型”列表中“使用公式确定要设置格式的单元格”，然后在下方的“为符合此公式的值设置格式”输入框中输入公式“=mod(row(),2)=0”。其用意是指定行号是偶数的单元格为要设置格式的单元格。 再点击对话框右下角的“格式”按钮，打开“设置单元格格式”对话框。点击“边框”选项卡，点击左边“颜色”下拉按钮，然后在列表中选中左上角的“白色，背景1”颜色，并在右侧“边框”预览窗口的四边边框线中点击下边线，然后再选择颜色为“白色，背景1，深色50%”，点击上边线。这样，就可以使单元格上边线为深灰色线，下边线为白色线，而左右两边均无边框。 一路确定，关闭全部的对话框后。然后，只要我们设置“行号为偶数且列数为奇数”或“行号为奇数且列数为偶数”的单元格左边框和上边框为深灰色，则下边框和右边框为白色，那么就可以得到想要的效果了。如果我们将上边框和左边框设置成白色，而下边框和右边框设置成深灰色，那么可以得到恰好相反的凹凸效果。此外，我们在指定边框线的同时，也可以指定单元格的填充颜色，那么就可以得到更为丰富的效果了。 如何在表头下面放图片【问题】为工作表添加的背景，是衬在整个工作表下面的，能不能只放在表头下面呢? 【解答】1.执行“格式工作表背景”命令，打开“工作表背景”对话框，选中需要作为背景的图片后，按下“插入”按钮，将图片衬于整个工作表下面。 2.在按住Ctrl键的同时，用鼠标在不需要衬图片的单元格（区域）中拖拉，同时选中这些单元格（区域）。 3.按“格式”工具栏上的“填充颜色”右侧的下拉按钮，在随后出现的“调色板”中，选中“白色”。 经过这样的设置以后，留下的单元格下面衬上了图片，而上述选中的单元格（区域）下面就没有衬图片了（其实，是图片被“白色”遮盖了）。 提示：衬在单元格下面的图片是不支持打印的。 Excel2007函数公式实例集对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)对生产表中大于100的产量进行求和:=SUM(B2:B11100)*B2:B11)对生产表大于110或者小于100的数据求和:=SUM(B2:B11110)*B2:B11)对一车间男性职工的工资求和:=SUM(B2:B10=一车间)*(C2:C10=男)*D2:D10)对姓赵的女职工工资求和:=SUM(LEFT(A2:A10)=赵)*(C2:C10=女)*D2:D10)求前三名产量之和:=SUM(LARGE(B2:B10,1,2,3)求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9)求图书订购价格总和:=SUM(B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2)用SUM函数计数:=SUM(B2:B9=男)*1)求1累加到100之和:=SUM(ROW(1:100)多个工作表不同区域求前三名产量和:=SUM(LARGE(CHOOSE(1,2,3,4,5,A组!B2:B9,B组!B2:B9,C组!B2:B9,D组!B2:B9,E组!B2:B9),ROW(1:3)计算仓库进库数量之和:=SUMIF(B2:B10,=进库,C2:C10)计算仓库大额进库数量之和:=SUMIF(B2:B8,1000)对1400到1600之间的工资求和:=SUM(SUMIF(B2:B10,&LARGE(B2:B10,4)+SUMIF(B2:B10,600)只汇总6080分的成绩:=SUMIFS(B2:B10,B2:B10,=60,B2:B10,25)*1)汇总一班人员获奖次数:=SUMPRODUCT(B2:B11=一班)*C2:C11)汇总一车间男性参保人数:=SUMPRODUCT(A2:A10&B2:B10&C2:C10=一车间男是)*1)汇总所有车间人员工资:=SUMPRODUCT(-NOT(ISERROR(FIND(车间,A2:A10),C2:C10)汇总业务员业绩:=SUMPRODUCT(B2:B11=江西,广东)*(C2:C11=男)*D2:D11)根据直角三角形之勾、股求其弦长:=POWER(SUMSQ(B1,B2),1/2)计算A1：A10区域正数的平方和:=SUMSQ(IF(A1:A100,A1:A10)根据二边长判断三角形是否为直角三角形:=CHOOSE(SUMSQ(MAX(B1:B3)=SUMSQ(LARGE(B1:B3,2,3)+1,非直角,直角)计算1到10的自然数的积:=FACT(10)计算50到60之间的整数相乘的结果:=FACT(60)/FACT(49)计算1到15之间奇数相乘的结果:=FACTDOUBLE(15)计算每小时生产产值:=PRODUCT(C2:E2)根据三边求普通三角形面积:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,1,2,3)0.5根据直角三角形三边求三角形面积:=PRODUCT(LARGE(B1:B3,2,3)/2跨表求积:=PRODUCT(产量表:单价表!B2)求不同单价下的利润:=MMULT(B2:B10,G2:H2)*25%制作中文九九乘法表:=COLUMN()&*&ROW()&=&MMULT(ROW(),COLUMN()计算车间盈亏:=SUM(MMULT(B3:E50)*B3:E5,1;1;1;1),MMULT(B3:E5=TRANSPOSE(ROW(2:11),B2:B11)计算每日库存数:=MMULT(N(ROW(2:11)=TRANSPOSE(ROW(2:11),B2:B11-C2:C11)计算A产品每日库存数:=MMULT(N(ROW(2:17)=TRANSPOSE(ROW(2:17),(B2:B17=A)*(C2:C17-D2:D17)求第一名人员最多有几次:=MAX(MMULT(N(B2:B7=TRANSPOSE(B2:B7),ROW(2:7)0)求几号选手选票最多:=RIGHT(MAX(MMULT(N(B2:B10=TRANSPOSE(B2:B10),ROW(2:10)0)*100+B2:B10)总共有几个选手参选:=SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10),ROW(2:10)0)在不同班级有同名前提下计算学生人数:=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17),ROW(2:17)0)计算前进中学参赛人数:=SUM(IFERROR(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)*(A2:A17=前进中学),ROW(2:17)0),0)串联单元格中的数字:=MMULT(10(COLUMNS(B:K)-COLUMN(C:L),TRANSPOSE(B2:K2)或=SUMPRODUCT(B2:K2,10(COLUMNS(B:K)-COLUMN(B:K)-1)计算达标率:=MMULT(TRANSPOSE(N(A2:A1160)*(B2:B1160)*(B2:B1180),ROW(2:11)0)汇总A组男职工的工资:=MMULT(TRANSPOSE(N(B2:B11&C2:C11=男A组)*D2:D11),ROW(2:11)0)计算象棋比赛对局次数l:=COMBIN(B1,B2)计算五项比赛对局总次数:=SUM(COMBIN(B2:B5,2)预计所有赛事完成的时间:=COMBIN(B1,B2)*B3/B4/60计算英文字母区分大小写做密码的组数:=PERMUT(B1*2,B2)计算中奖率:=TEXT(1/PERMUT(B1,B2),0.00%)计算最大公约数:=GCD(B1:B5)计算最小公倍数:=LCM(B1:B5)计算余数:=MOD(A2,B2)汇总奇数行数据:=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)根据单价数量汇总金额:=SUMPRODUCT(MOD(COLUMN(A:I),2)*A2:I2,(MOD(COLUMN(B:J),2)=0)*B2:J2)设计工资条:=IF(MOD(ROW(),3)=1,单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明细!A$1,ROW()/3+1,0),)根据身份证号计算性别:=IF(MOD(MID(B2,15,3),2),男,女)每隔4行合计产值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,4,),D2*E2)工资截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,10),10)汇总3的倍数列的数据:=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10)将数值逐位相加成一位数:=IF(A2=0,0,MOD(A2-1,9)+1)计算零钞:5角=INT(MOD(SUM(B2:B10),1)/0.5);2角=INT(MOD(MOD(SUM(B2:B10),1),0.5)/0.2);1角=MOD(MOD(MOD(SUM(B2:B10),1),0.5),0.2)/0.1秒与小时、分钟的换算:=QUOTIENT(MOD($A2,IF(COLUMN()=2,A2+1,60(3-COLUMN(A:A)+1),60(3-COLUMN(A:A)生成隔行累加的序列:=QUOTIENT(ROW()+1,2)根据业绩计算业务员奖金:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2计算预报温度与实际温度的最大误差值:=MAX(ABS(C2:C8-B2:B8)计算个人所得税:=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)产生100到200之间带小数的随机数:=RAND()*(200-100)+100产生ll到20之间的不重复随机整数:=RANK(A2:A11,A2:A11)+10将20个学生的考位随机排列:=INDEX(A$2:A$11,RANK(H2:H11,H2:H11)将三个学校植树人员随机分组:=OFFSET(A$1,RANK(G2,G$2:G$11),)&:&OFFSET(B$1,RANK(G2,G$2:G$11),)&:&OFFSET(C$1,RANK(G2,G$2:G$11),)产生-50到100之间的随机整数:=RANDBETWEEN(-50,100)产生1到100之问的奇数随机数:=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100)产生1到10之间随机不重复数:=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10)=0,ROW($1:$10),RANDBETWEEN(1,12-ROW()根据三角形三边长求证三角形是直角三角形:=IF(POWER(MAX(B1:B3),2)=SUM(POWER(LARGE(B1:B3,2,3),2),是,不是)计算Al:A10区域开三次方之平均值:=AVERAGE(POWER(A1:A10,1/30)计算Al:A10区域倒数之积:=PRODUCT(POWER(A1:A10,-1)根据等边三角形周长计算面积:=SQRT(B1/2*POWER(B1/2-B1/3,3)抽取奇数行姓名:=INDEX(B:B,ODD(RANDBETWEEN(1,ROWS(1:12)-1)统计A1:B10区域中奇数个数:=SUMPRODUCT(N(ODD(A1:B10)=(A1:B10)统计参考人数:=SUMPRODUCT(EVEN(COLUMN(A1:J12)=COLUMN(A1:J12)*(MOD(ROW(A1:J12),3)=1)*(A1:J12)计算A1:B10区域中偶数个数:=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10)合计购物金额、保留一位小数:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1)将每项购物金额保留一位小数再合计:=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1)将金额进行四舍六入五单双:=IF(A2-TRUNC(A2,1)=0.06,TRUNC(A2,1)+0.1,TRUNC(TRUNC(A2,1)+0.1)/2,1)*2)根据重量单价计算金额，结果以万为单位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000计算年假天数:=TRUNC(TODAY()-B2)*(TODAY()-B2)=365)/365*5)根据上机时间计算上网费用:=(TRUNC(B2)+(B2-TRUNC(B2)=0.5)*1.5+(MOD(B2,1)0)成绩表转换:=INDEX($A:$E,CEILING(ROW()*3/5,3)-(COLUMN()=7),MOD(ROW(B2)-1,5)+1)计算机上网费用:=CEILING(B2,30)/30*2统计可组建的球队总数:=SUMPRODUCT(FLOOR(B2:B10,5)/5)统计业务员提成金额，不足20000元忽略:=FLOOR(B2,20000)/20000*500FLOOR函数处理正负数混合区域:=FLOOR(A1*100,10*(IF(A10,1,-10)将数据转换成接近6的倍数:=MROUND(A1,6)以超产80为单位计算超产奖:=SUM(MROUND(B2:B11-700,80*IF(B2:B11=700,1,-1)/80*50将统计金额保留到分位:=ROUND(SUMPRODUCT(B2:B10,C2:C10),2)将统计金额转换成以万元为单位:=ROUND(SUMPRODUCT(B2:B10,C2:C10)%,)对单价计量单位不同的品名汇总金额:=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=G,1000,1),(D2:D10=G)*2)将金额保留“角”位，忽略“分”位:=SUM(ROUNDDOWN(B2:B10*C2:C10,1)计算需要多少零钞:=SUM(ROUNDDOWN(B2:B10*C2:C10,0,-1)*1,-1)计算值为l万的整数倍数的数据个数:=SUM(N(B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)计算完成工程需求人数:=SUM(ROUNDUP(B2:B11/C2:C11,)按需求对成绩进行分类汇总:=SUBTOTAL(HLOOKUP(G$1,平均成绩,科目数量,最高成绩,最低成绩,成绩合计;1,2,4,5,9,2,0),B2:D2)不间断的序号:=SUBTOTAL(103,$B$2:B2)仅对筛选出的人员排名次:=CONCATENATE(第,SUM(N(IF(SUBTOTAL(103,OFFSET(优等生!A$1,ROW($2:$31)-2,)=1,$C$2:$C$31,)C2)+1,名)判断两列数据是否相等:计算两列数据同行相等的个数:=SUM(N(A1:A10=B1:B10)计算同行相等且长度为3的个数:=SUM(A1:A10=B1:B10)*(LEN(A1:A10)=3)提取A产品最后单价:=INDEX(C:C,MAX(B2:B10=A)*ROW(2:10)判断学生是否符合奖学金发放条件:=AND(B290,C2汉族)所有裁判都给“通过”就进入决赛:=AND(B2:E2=通过)判断身份证长度是否正确:=OR(LEN(B2)=15,18)判断歌手是否被淘汰:=OR(B2:E2=不通过)根据年龄判断职工是否退休:=OR(AND(B2=男,C260),AND(B2=女,C255)根据年龄与职务判断职工是否退休:=OR(AND(B2=男,D260+(C2=干部)*3),AND(B2=女,D255+(C2=干部)*3)没有任何裁判给“不通过”就进行决赛:=NOT(OR(B2:E2=不通过)计彝成绩区域数字个数:=SUM(NOT(ISERROR(NOT(B2:B11)*1)评定学生成绩是否及格:=IF(AVERAGE(B2:D2)=60,及格,不及格)根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)60,不及格,IF(AVERAGE(B2:D2)90,良好,IF(AVERAGE(B2:D2)80000,1000,500)根据工作时间计算12月工资:=C2+SUM(IF(B20,1,3,5,10,300,500,500,500,500)合计区域的值并忽略错误值:=SUM(IF(ISERROR(A1:C10),0,A1:C10)既求积也求和:=IF(D2,PRODUCT(C2:D2),SUM(OFFSET(E2,-3,3)分别统计收入和支出:收入=SUM(IF(B2:B130,B2:B13);支出=SUM(IF(SUBSTITUTE(IF(B2:B13,B2:B13,0),负,-)*1COUNT(B$2:B$11),LARGE(B$2:B$11,ROW(A1)排除空值:=INDEX($A:$B,SMALL(IF($B$1:$B$11,ROW($1:$11),ROWS($1:$11)+1),ROW(),COLUMN(B2)&有选择地汇总数据:=SUM(IF(A2:A11=A组,C组,C2:C11)混合单价求金额合计:=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10=K,1000,1),2)计算异常停机时间:=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11,C2:C11,0),修机,),换原料,)*1)计算最大数字行与文本行:=MAX(IF(B:B,ROW(A:A)找出谁夺冠次数最多:=INDEX(B:B,MIN(IF(MAX(COUNTIF(B2:B12,B2:B12)=COUNTIF(B2:B12,B2:B12),ROW(2:12)将全角字符转换为半角:=ASC(A2)计算汉字全角半角混合字符串中的字母个数:=LEN(ASC(A2)*2-LENB(ASC(A2)将半角字符转换成全角显示:=WIDECHAR(A2)计算混合字符串中汉字个数:=LEN(A2)-(LENB(WIDECHAR(A2)-LENB(ASC(A2)判断单元格首字符是否为字母:=OR(AND(CODE(A2)64,CODE(A2)96,CODE(A2)47)*(CODE(MID(A2,ROW(INDIRECT(1:&LEN(A2),1)64)*(CODE(UPPER(MID(A2,ROW(INDIRECT(1:&LEN(A2),1)91)产生大、小写字母A到Z的序列:大写字母=CHAR(ROW(A65)，小写字母=CHAR(ROW(A65)+32)产生大写字母A到ZZ的字母序列:=IF(ROW()26,CHAR(MOD(ROW()-1,26)+65),)产生三个字母组成的随机字符串:=CHAR(RANDBETWEEN(65,90)&CHAR(RANDBETWEEN(65,90)&CHAR(RANDBETWEEN(65,90)用公式产生换行符:=A2&CHAR(10)&B2将数字转换成英文字符:字符码=RANDBETWEEN(1,100)，升序位置=CHAR(MOD(A1-1,26)+65)将字母升序排序:=CHAR(SMALL(CODE(A$2:A$13),ROW(A1)返回自动换行单元格的第二行数据:=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2)根据身份证号码提取出生年月日:=CONCATENATE(MID(B2,7,4-2*(LEN(B2)=15),年,MID(B2,11-2*(LEN(B2)=15),2),月,MID(B2,13-2*(LEN(B2)=15),2),日 )计算平均成绩及评判是否及格:=CONCATENATE(INT(AVERAGE(B2:D2),: ,IF(AVERAGE(B2:D2)=60,不),及格)提取前三名人员姓名:=CONCATENATE(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,1),A2:A11),|,LOOKUP(0,0/(B2:B11=LARGE(B2:B11,2),A2:A11),|,(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,3),A2:A11)将单词转换成首字母大写:=PROPER(A2)将所有单词转换成小写形式:=LOWER(A2)将所有句子转换成首字母大写其余小写:=CONCATENATE(PROPER(LEFT(A2),LOWER(RIGHT(A2,LEN(A2)-1)将所有字母转换成大写形式:=UPPER(A2)计算字符串中英文字母个数:=SUM(N(NOT(EXACT(UPPER(MID(A2,ROW(INDIRECT(1:&LEN(A2),1),LOWER(MID(A2,ROW(INDIRECT(1:&LEN(A2),1)计算字符串中单词个数:=SUM(N(EXACT(TRIM(MID(UPPER(A2),ROW(INDIRECT(1:&LEN(A2),1),MID(PROPER(A2),ROW(INDIRECT(1:&LEN(A2),1)将文本型数字转换成数值:=SUM(VALUE(B2:B10)计算字符串中的数字个数:=SUMPRODUCT(N(ISNUMBER(VALUE(MID(A2,ROW($1:$100),1)*1)提取混合字符串中的数字:=MAX(IFERROR(VALUE(MID(A2,MIN(FIND(0;1;2;3;4;5;6;7;8;9,A2&1234567890),ROW(INDIRECT(1:&LEN(A2),0)串联区域中的文本:=CONCATENATE(T(A2),T(B2),T(C2)给公式添加运算说明:=CONCATENATE(你好,B2,2008)&T(N(公式含义:连接“你好”和单元格B2、“2008”)根据身份证号码判断性别:=TEXT(MOD(MID(B2,15,3),2),=1男;=0女)将所有数据转换成保留两位小数再求和:=SUM(-TEXT(B2:B11*C2:C11,0.00)将货款显示为“万元”为单位:=TEXT(B2,￥#&.&#,万元)根据身份证号码计算出生日期:=IF(LEN(B2)=15,19,)