Excel函数应用实例
守护时间
2017-05-14 05:01
统计偶数单元格合计数值 解答：统计F4到F62的偶数单元格合计数值 。公式一｛=SUM(IF(MOD(ROW(F4:F62),2)=0,F4:F62))｝ 公式二 (要有安装'分析工具箱')｛=SUM(IF(ISEVEN(ROW(F4:F62)),F4:F62))｝ 用EXCEL做统计 如：A1，B1单元格是时间类型，C1是数字类型，我要计算费用用（B1-A1）*C1得到的数据还是时间类型、怎么办 ？我希望把时间类型变为整数类型，如0：50（50分钟）*3（元/H）=2.5元 解答：设A1为3：30，B1为4：30，C1为3，D1为下列输入的函数： =HOUR(B1)*60+MINUTE(B1)-(HOUR(A1)*60+MINUTE(A1)) 。D1结果等于60（分钟） 根据A1的内容,决定A2的数值是来自sheet1,sheet2还是sheet3. 解答：if(a1=1,sheet1!a1,if(a1=2,sheet2!a1,if(a1=3,sheet3!a1))) 直接输入一组数如“20020213101235”后，自动转换成日期格式 解答：A1中输入，B1中转换。。B1=Left(A1,4)&"—"&MID(A1,5,2)&"—"&MID(A1,7,2)&" "&MID(A1,9,2)&":"&MID(A1,11,2)&":"&MID(A1,13,2) 把sheet1到sheet200的a19这一格，依序贴到sheet0的a1到a200 解答：方法一：公式 可在A1储存格输入以下公式，再行拖曳至A200即可。 =INDIRECT("Sheet"&ROW()&"!A19") 方法二：VBA Sub Macro1() '选择工作表 sheet0 Sheets("sheet0").Select For r = 1 To 200 '将工作表 1~200 里面的 D17 复制到 sheet0 的 A1~A200 Cells(r, 1) = Worksheets(CStr(r)).Range("D17") On Error Resume Next Next r End Sub 如果有文本串"YY0115"，我想取第三、四的值"01"，应该用什么函数 解答：1、=mid("YY0115",3,2)&"" 在一个表中有两列日期型数字请问如何在第三列中得到其差(两日期间的天数) 答：=IF(A1>B1,DATEDIF(B1,A1,"d"),DATEDIF(A1,B1,"d")) 重要的EXCEL文件坏了(文件带密码)有没有EXCEL修复工具 解答：Excel 2000 数据>>取得外部数据>>新增数据库查询>>Excel File*>>找到档案>> [选项]勾选所有选项>> 会找到所有未命名[区块]如Sheet1$ >>找到字段>>其它跟着查询精灵导引一步一步作 此中间层组件叫作Microsoft Query 可以用来拯救[毁损档案] 怎样用函数来显示某月的最后一天是几号 答：= DAY(DATE(年份,月份+1,1)-1) 请问如何在函数中同时使用两个条件 例:在IF同时使用条件B1>0和B1<10 解答：and(B1>0,B1<10) 用TRIM把“中 心 是”中间的空格去掉 解答：用SUBSTITUDE（）函数，多少空格都能去掉。如A1中有：中 心 是 则在B1中使用=SUBSTITUTE(A1," ","")就可以了。注意：公式中的第一个“ ”中间要有一个空格，而第二个“”中是无空格的。 如何统计某个列有数字的个数 解答：=COUNT(A:A) 如何统计此次自动筛选 出来共有的记录条数 解答：用 counta 统计 如何判断某个单元格包含某个字符 解答：设A1=LOVE，查找字母L是否在A1中， =IF(ISERROR(SEARCH("L",A1)),"NO","YES") 在单元格返回工作表名称 解答：函数方法： =MID(CELL("filename"),FIND("]",CELL("filename"))+1,100) 使用vlookup函数的问题 当时有两千多人的考试成绩要与花名册挂接，考试成绩放在sheet km1中，花名册放在sheet hmc中，他们共有字段为准考证号，我的想法是根据准考证号，用vlookup函数查找相应的成绩并放在相应的人员下。 sheet km的准考证号放在第一列，考试成绩放在第二列，查找范围是$a$2:$b$2265，sheet hmc的准考证号党在第一列。 公式为： vlookup(a2,km!$a$2:$b$2265,2,false) 公式应该没什么问题，但只能找到很少的纪录(<60),究竟是什么地方除了问题，请高手指点！ 解答1：可以用SUMIF函数解决： =SUMIF(km!$A$2:$B$2131,A2,km!$B2:$B$2131) (作者注：将sheet km下的所有准考证号都转化为文本，再使用vlookup函数，一切正常！vlookup函数查找区域必须转化为文本！) 20列间隔3列求和 解答：假设a1至t1为数据（共有20列），在任意单元格中输入公式：=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1)) 按ctrl＋shift＋enter结束即可求出每隔三行之和。 WORD向EXCEL格式转换时一个单词或汉字分别填入EXCEL中的一个单元格中 解答：在WORD中利用“替换”功能，把每个字符后面都加上一制表符，（在“查找”框中单击“高级”按钮，弹出“高级对话框”，把光标置入“查找”框内，在“特殊字符”中单击“任意字符”，再把光标置入“替换”框内，在“特殊字符”中单击“要查找的文字”，再单击“制表符”，再全部替换） 然后复制或剪切，在EXCEL中粘贴即可。 用字母在单元格里面填入平方米 解答1：在编辑状态下，选中“2”，按鼠标右键，选择“设置单元格格式”，选“上标” 解答2：按[ALT]+数字键178[ENTER] 从身份证号码中提取性别 Q： A1单元格中是15位的身份证号码，要在B1中显示性别(这里忽略15位和18位身份证号码的判别） B1=if(mod(right(A1,1),2)>0,"male","female")请问这个公式有无问题，我试过没发现问题。但在某个网站看到作者所用的是如下公式： B1=if(mid(A1,15,1)/2=trunc(mid(A1,15,1)/2）,"female","male") 用函数将输入的月份转换为这个月所包含的天数 假设A1单元格为月份： ＝TEXT((DATE(YEAR(NOW()),A1+1,1)-1),"d") 或：=DAY(DATE(YEAR(NOW()),A1+1,0)) 如何自动填充内容 A1：A20是编号，B1：B20是姓名，C1：C20是性别，当我在A21单元格输入A1：A20范围内的任意一个编号时，B21出现对应的姓名，C21出现对应的性别。该如何做，请帮忙。 解答：B21单元格公式“=IF(A21=0,"",VLOOKUP(A21,A1:C20,2,FALSE))”；C21单元格公式“=IF(A21=0,"",VLOOKUP(A21,A1:C20,3,FALSE))”这个公式也适用于A列编号不排序的情况，如果升序的话会更简单一点。 如何在excel中已有的数值前加零变成六位 比如说 25、369、1569等，操作后变成000025、000369、00156 解答：如果直接输入的话，可以在数值前面加“'”，如“'002020”； 如果处理现成的数据，或者从别处（比如从A1单元格）链接来的数据，可以用公式： ＝RIGHT("00000"&A1,6) 一次删完Excel里面多出很多的空白行 解答：1、用分面预览看看 2、用自动筛选然后删除 3、用自动筛选，选择一列用非空白，空白行就看不到了，打印也不会打出来。但是实际上还是在的，不算删除。或者用自动筛选选择空白将空白行全显出来一次删完也可以。 4、先插入一列，在这一列中输入自然数序列，然后以任一列排序，排序完后删除数据后面的空行，再以刚才输入的一列排序，排序后删除刚才插入的一列。 表1、表2分别有20个人的基本情况和其中10个人的名字，让表1的数据自动填充到表2 答：1、用lookup函数即可。要保证20人不重名； 2、假设表1的D列对应表2的E列。E2的公式：=VLOOKUP(B2,Sheet1!B:D,3,FALSE) 使用vlookup函数返回#N/A符号时将此符号用0或空格来代替 答：这样处理： =IF(ISNA(VLOOKUP(C13,A1:B10,2)),0,VLOOKUP(C13,A1:B10,2)) 或：IF(ISERROR(vlookup(a1,e1:g10,2,0)),0,vlookup(a1,e1:g10,2,0))。 通过条件格式将小计和总计的行设为不同的颜色 答：输入=RIGHT(RC,1)="计"；设定字体、边框、图案；确定。 复制隐藏后的表格到一个新表格中使被隐藏的内容不显示 答：crtl+g－选可见单位格－复制－粘贴。 如何将一个工作簿中的一个Sheet隐藏 答：1、选“格式”---“工作表”----“隐藏” 2、使用VBA这样隐藏后在使用工作表保护。 Alt+F11----Ctrl+G----出现立即执行窗口，在此窗口内执行 Sheet1.Visible = xlSheetVeryHidden 这样隐藏后sheet在格式---工作表----取消隐藏是看不见的。 工资条问题 职工工资构成非常复杂，往往超过10项，因此每月发工资时要向职工提供一包含工资各构成部分的项目名称和具体数值的工资条。打印工资条时要求在每个职工的工资条间有一空行便于彼此裁开。本模板就是用EXCEL函数根据工资清单生成一便于分割含有工资细目的工资条表格。 本工资簿包含两张工资表。第1张工资表就是工资清单，称为"清单"。它第一行为标题行包括职工姓名、各工资细目。 第2张工作表就是供打印的表，称为"工资条"。它应设置为每三行一组，每组第一行为标题，第二为姓名和各项工资数据，第三行为空白行。就是说整张表被3除余1的行为标题行，被3除余2的行为包括职工姓名、各项工资数据的行，能被3整除的行为为空行。 在某一单元格输入套用函数"=MOD(ROW(),3)"，它的值就是该单元格所在行被3除的余数。因此用此函数能判别该行是标题行、数据行还是空行。 在A1单元格输入公式"=IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,清单!A$1,"value-if-false"))"并往下填充，从A1单元格开始在A列各单元格的值分别为清单A1单元格的值即姓名、value-if-false、空白，姓名、value-if-false、空白，......。其中value-if-false表示MOD(ROW(),3)既不等于0又不等于1时，即它等于2时应取的值。它可用如下函数来赋值："INDEX(清单!$A:$G,INT((ROW()+4)/3),COLUMN())"。INDEX()为一查找函数它的格式为：INDEX(reference,row-num,col-num),其中reference为查找的区域，本例中为清单表中的A到G列，即函数中的"清单!$A:$G"，row-num为被查找区域中的行序数即函数中的INT((ROW()+4)/3)，col-num为被查找区域中的列序数即函数中的COLUMN()。第2、5、8.......行的行号代入INT((ROW()+4)/3)正好是2、3、4......，COLUMN()在A列为1。因此公式"=INDEX(清单!$A:$G,INT((ROW()+4)/3),COLUMN())"输入A列后，A2、A5、A8......单元格的值正好是清单A2、A3、A4......,单元格的值。这样，表的完整的公式应为"=IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,清单!A$1,INDEX(清单!$A:$G,INT((ROW()+4)/3),COLUMN())))"。把此公式输入A1单元格，然后向下向右填充得到了完整的工资条表。 为了表格的美观还应对格式进行设置，一般习惯包括标题、姓名等文字在单元格中要取中，数字要右置，数字小数点位数也应一致，还有根据个人的爱好设置边框。本表格只需对一至三行的单元格进行设置，然后通过选择性格式设置完成全表的设置。 本工作簿的特点是1、不对清单表进行操作保持清单工作表的完整，2、全工作表只有一个公式通过填充得到全表十分方便。 例如：我的单位不大不小，有200多号人。最近领导要求把每个员工个人的工资情况打出来，分发给每位员工。每个员工的工资条上只能有两行内容：一行是分解的项目内容，如基本工资、岗位工资、总计等等；另一行是对应第一行的具体工资数额。 可以这样解决：Sheet2 1.为A1命名为K 2.为A3:A250命名为XX 从A3贴上主索引,数据要连续中间不允许有空格 3.写公式=VLOOKUP(K,DATA,2,0) 有几个字段写几个,位置随您高兴摆 4.隐藏A栏 ************************************************************************************** Sub 打印() Application.ScreenUpdating = False '屏幕不更新 Dim c As Object '宣告c为对象,请准备空间 [xx].Select '选取变量范围 Set c = ActiveCell '设定c对象为作用单元格 Do Until IsEmpty(c.Value) '作Do循环直到无值时跳出 [k].Value = c.Value Set c = c.Offset(1, 0) '设定c往下进一格再取主索引值 Sheets("Sheet2").PrintPreview '工作表直接打印改PrintOut Loop End Sub 另一回答：我是做人事管理的也遇到过你的问题,我用如下方法解决十分方便,而不用任何代码. 方法的原理是调整打印机的自定义纸张大小到恰好显示一个人的工资条的大小,请按如下： 如：我的excel工资表将项目内容放在第一、二行，行高为20.1，用a4纸横向打印 1、在页面设置中将上、下边距，页眉、页脚均设为零， 在页面设置--工作表---顶端标题行 中输入 $1:$2,即将放在 第 一、二行的项目内容设成每页标题行 打印方向为横向 2、、在文件----打印---属性---纸张----自定义中将纸张的 宽度=280 （单位：毫米） 长度=2970 （单位：毫米） 3、打印时可选1---200页，即可打印200人的工资条，一张a4可打10人 确定后预览，可调整下边距至每页显示一张工资条 我的解决办法：我只用了一个公式: if(mod(row(),3)=0,"",if(mod(row)(),3)=1,sheet1!a$1,index(sheet1!$a:$g,int((row()+4/3),cllolumn())))你试一下 (解释：int((row()+4/3) 是这个意思：一个工资表，有列标题，接下来是工资记录。而我在此表基础上，加一个自动生 成的工资 条表， mod(row(),3)=0，在此表上用这个表示第三行保留空白行； if(mod(row)(),3)=1,sheet1!a$1，表示是第一行取标题列； index(sheet1!$a:$g,int((row()+4/3),column()）这是关键的地方：是指它不是第一行，也不三倍数的行，是记录 行的表示，你想第二行显示记录，则2+4/3=2 取工资 表的第二行记录；第五行显示记录，则5+4/3=3 取工资 表的第三行记录；第八行显示记录，则8+4/3=4 取工资 表的第四行记录；第十一行显示记录，则11+4/3=5 取工资 表的第五行记录；这个公式你可以根据具体情况变化：尤其是((row()+4/3),中的4这个数字， 定制单元格数字显示格式 定制单元格数字显示格式，先选择要定制的单元格或区域，》单击鼠标右键》单元格格式》选择‘数字’选项》选择‘自定义’》在“类型”中输入自定义的数字格式。 如何输入自定义的数字格式：需要先知道自定义格式中那些常用符号的含意，具体可以先不选择‘自定义’，而选择其它已有分类观看‘示例’，以便得知符号的意义。 比如：先选择‘百分比’然后马上选择‘自定义’，会发现‘类型’中出现‘0.00%’，这就是百分比的定义法，把它改成小数位3位的百分比显示法只要把‘0.00%’改成‘0.000%’就好了，把它改成红色的百分比显示法只要把‘0.00%’改成‘[红色]0.00%’就好了。 关于数据引用的问题 在一个工作簿中，假如A工作表中的单元格E8被B工作表中的某单元格引用，现在由于A工作表中插入了行，原来的E8现在可能是E28,结果造成B工作表引用数据错误，请问：如何可以使B工作表中的引用随着A表的变化也作相应的自动调整？ 回答：利用“相对引用”来实现，“相对引用”是Excel中默认的引用方式 例：在工作表Sheet1中Ｃ２单元格为５，Ｄ２单元格为６，在工作表Sheet2中Ｃ３单元格中输入“＝Sheet1!C2+Sheet1!D2”，如果把 Ｃ２单元格剪切到Ｃ３，那么在工作表Sheet2中Ｃ３单元格中的公式就自动变为“＝Sheet1!C3+Sheet1!D2” |
|
|
