下载

3下载券

加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 EXCEL使用技巧集合(目前见到最实用的)Excel问题集合

EXCEL使用技巧集合(目前见到最实用的)Excel问题集合.doc

EXCEL使用技巧集合(目前见到最实用的)Excel问题集合

学海无崖168
2018-09-04 0人阅读 举报 0 0 暂无简介

简介:本文档为《EXCEL使用技巧集合(目前见到最实用的)Excel问题集合doc》,可适用于IT/计算机领域

《xcel问题集合》程香宙编辑目录引用问题如何在一列列出工作薄中的所有表(表名无规律)位身份证号码输入的几种解决方法用countif算出数值大小在~之间的单元格个数几个工作表生成一个打印文件一次打印自动计算应收款滞纳金SUM函数难题工作表中的小写字母都变成大写用COUNTIF计算整个B列中含有“F”这个字符的单元格的个数自动记录出车情况整点记录坐标值请问如何把在Excel中绘制的曲线图表另存为jpg或gif格式的图片round函数工作表引用汇总在EXCEL中可否实现票面分解年龄及奖学金问题隔行求和问题在EXCEL里取数值的一部分用什么函数用什么函数可以插入字符将文件保存为以某一单元格中的值为文件名的宏怎么写求余数的函数评委评分数据校对如何在一个单元格中自动输入在另外一个单元格中输入的文字在表格中查找我需要的东西并把该行所有的数据反映到另外的表格中去在EXCEL表里建立唯一索引在该列输入重复的数值后自动提示输入错误发票小写金额填充排列问题在条件语句中如何实现符合某个时期的条件的记录进行统计行改列(不是转置问题)如何给自动筛选中的自定义指定快捷键汇总问题统计包含某个字符的个数如何用if函数判断,如果a单元格大于,b单元格为是错误,为非是正确我改进以下一个验证先进先出法公式有无简结一点的公式求如:a*ba*bb*b的和工作中须将表格中大量的日期同时转换为中英文的星期几我想查询表格中的某一列编号中有这一编号时引用其同行某列的数值如何给自定义函数写上帮助信信息和参数说明如何在EXCEL中统计一个数在哪一行出现在SHEET的A做公式加总SHEET:SHEET的A怎么做啊工作表间的数据输入请问如何用公式达到我想要的结果一个页面在打印一次后是否能在上面的某一单元格数值上加不用VBA用EXCEL语句的IF函数如何使用其条件关系求和问题排名问题统计满足条件的数据并生成新的数据列工作表间的查找与复制比较运算符为何要用引号EXCEL中如何修改批注的字体和颜色如何以进阶筛选命令筛选出“序号”为单数、双数、或者是任意间距值的recordsn=sum(b到m)、n=sum(b到m)、有没有办法让n那一列可以自动填充在工作表里有连续行数据,现在要每行间格行,要如何做当点到某单元格时单元格会出现一个下拉的列框可以选取其中的数据查找与引用如果D>那E就显示$、如果D>那E就显示$依此类推在两个工作表strrmbdx"整"EndIfrmbdx=astrrmbdxEndFunction方法=IF(ISTEXT(C),"","人民币:"TEXT(INT(C),"dbnum")"元"IF(INT(C*)INT(C)*=,"",TEXT(INT(C*)INT(C)*,"dbnum")"角")IF(INT(C*)INT(C*)*=,"整",TEXT(INT(C*)INT(C*)*,"dbnum")"分"))……还有很多方法编者没有收集。中国式的排名 函数应用RANK是个排名函数但有一个问题象上面有两个分也就是两并列第一排名就从第三名开始没有第二名也许这个美国式的排名中国式的排名前面有两个一样的分数并列第一后是第二名怎样用函数实现解答:在B中复制如下的数组公式然后往右复制:{=SUM(IF(B=LARGE(IF(LARGE($B$:$H$,COLUMN($A$:$G$))=IF(ISERROR(LARGE($C$:$I$,COLUMN($A$:$G$))),,LARGE($C$:$I$,COLUMN($A$:$G$))),,LARGE($B$:$H$,COLUMN($A$:$G$))),COLUMN($A$:$G$)),COLUMN($A$:$G$),))}也可换个方式不用数组在B中复制如下的公式然后往右复制:=RANK(B,$B:$H)COUNTIF($B$:B,B)当做日报表累计数自动加当做日报表时,怎样让月累计数自动加上就是要月累计自动加上今天的当日收入数,今天只输入当日收入,我想用用前一天的月累计数加上今天的当日收入数为今天的月累计数情况是一月一个工作薄,每一个工作薄下个工作表,用每一天的日期为报表名我想把月累计的公式写为=sheet!c中的用day(now())的值去取代他,该怎么办呢以下为报表格式,谢谢大家帮我想一想部门当日收入月累计团队收入写字间收入,房内吧收入,,解答:=SUM('Sheet:Sheet'!C)在你需要月汇总的单元格填入上述公式其作用是将工作表到工作表的“C”单元格的值全部累加起来而“C”单元格应填入当日的收入数。又问:月累计的公式写为=sheet!c中的用day(now())的值去取代他,该怎么办呢”答:用公式:=INDIRECT(ADDRESS(,,,,"sheet"DAY(NOW())))工资条问题职工工资构成非常复杂往往超过项因此每月发工资时要向职工提供一包含工资各构成部分的项目名称和具体数值的工资条。打印工资条时要求在每个职工的工资条间有一空行便于彼此裁开。本模板就是用EXCEL函数根据工资清单生成一便于分割含有工资细目的工资条表格。本工资簿包含两张工资表。第张工资表就是工资清单称为"清单"。它第一行为标题行包括职工姓名、各工资细目。第张工作表就是供打印的表称为"工资条"。它应设置为每三行一组每组第一行为标题第二为姓名和各项工资数据第三行为空白行。就是说整张表被除余的行为标题行被除余的行为包括职工姓名、各项工资数据的行能被整除的行为为空行。在某一单元格输入套用函数"=MOD(ROW(),)"它的值就是该单元格所在行被除的余数。因此用此函数能判别该行是标题行、数据行还是空行。在A单元格输入公式"=IF(MOD(ROW(),)=,"",IF(MOD(ROW(),)=,清单!A$,"valueiffalse"))"并往下填充从A单元格开始在A列各单元格的值分别为清单A单元格的值即姓名、valueiffalse、空白姓名、valueiffalse、空白。其中valueiffalse表示MOD(ROW(),)既不等于又不等于时即它等于时应取的值。它可用如下函数来赋值:"INDEX(清单!$A:$G,INT((ROW())),COLUMN())"。INDEX()为一查找函数它的格式为:INDEX(reference,rownum,colnum),其中reference为查找的区域本例中为清单表中的A到G列即函数中的"清单!$A:$G"rownum为被查找区域中的行序数即函数中的INT((ROW()))colnum为被查找区域中的列序数即函数中的COLUMN()。第、、行的行号代入INT((ROW()))正好是、、COLUMN()在A列为。因此公式"=INDEX(清单!$A:$G,INT((ROW())),COLUMN())"输入A列后A、A、A单元格的值正好是清单A、A、A,单元格的值。这样表的完整的公式应为"=IF(MOD(ROW(),)=,"",IF(MOD(ROW(),)=,清单!A$,INDEX(清单!$A:$G,INT((ROW())),COLUMN())))"。把此公式输入A单元格然后向下向右填充得到了完整的工资条表。为了表格的美观还应对格式进行设置一般习惯包括标题、姓名等文字在单元格中要取中数字要右置数字小数点位数也应一致还有根据个人的爱好设置边框。本表格只需对一至三行的单元格进行设置然后通过选择性格式设置完成全表的设置。本工作簿的特点是、不对清单表进行操作保持清单工作表的完整、全工作表只有一个公式通过填充得到全表十分方便。例如:我的单位不大不小有多号人。最近领导要求把每个员工个人的工资情况打出来分发给每位员工。每个员工的工资条上只能有两行内容:一行是分解的项目内容如基本工资、岗位工资、总计等等另一行是对应第一行的具体工资数额。可以这样解决:Sheet为A命名为K为A:A命名为XX从A贴上主索引,数据要连续中间不允许有空格写公式=VLOOKUP(K,DATA,,)有几个字段写几个,位置随您高兴摆隐藏A栏**************************************************************************************Sub打印()ApplicationScreenUpdating=False'屏幕不更新DimcAsObject'宣告c为对象,请准备空间xxSelect'选取变量范围Setc=ActiveCell'设定c对象为作用单元格DoUntilIsEmpty(cValue)'作Do循环直到无值时跳出kValue=cValueSetc=cOffset(,)'设定c往下进一格再取主索引值Sheets("Sheet")PrintPreview'工作表直接打印改PrintOutLoopEndSub另一回答:我是做人事管理的也遇到过你的问题,我用如下方法解决十分方便,而不用任何代码方法的原理是调整打印机的自定义纸张大小到恰好显示一个人的工资条的大小,请按如下:如:我的excel工资表将项目内容放在第一、二行行高为用a纸横向打印、在页面设置中将上、下边距页眉、页脚均设为零在页面设置工作表顶端标题行中输入$:$,即将放在第一、二行的项目内容设成每页标题行打印方向为横向、、在文件打印属性纸张自定义中将纸张的宽度=(单位:毫米)长度=(单位:毫米)、打印时可选页即可打印人的工资条一张a可打人确定后预览可调整下边距至每页显示一张工资条我的解决办法:我只用了一个公式:if(mod(row(),)=,"",if(mod(row)(),)=,sheet!a$,index(sheet!$a:$g,int((row()),cllolumn())))你试一下(解释:int((row())是这个意思:一个工资表有列标题接下来是工资记录。而我在此表基础上加一个自动生成的工资条表mod(row(),)=在此表上用这个表示第三行保留空白行if(mod(row)(),)=,sheet!a$表示是第一行取标题列index(sheet!$a:$g,int((row()),column())这是关键的地方:是指它不是第一行也不三倍数的行是记录行的表示你想第二行显示记录则=取工资表的第二行记录第五行显示记录则=取工资表的第三行记录第八行显示记录则=取工资表的第四行记录第十一行显示记录则=取工资表的第五行记录这个公式你可以根据具体情况变化:尤其是((row()),中的这个数字定制单元格数字显示格式定制单元格数字显示格式先选择要定制的单元格或区域》单击鼠标右键》单元格格式》选择‘数字’选项》选择‘自定义’》在“类型”中输入自定义的数字格式。如何输入自定义的数字格式:需要先知道自定义格式中那些常用符号的含意具体可以先不选择‘自定义’而选择其它已有分类观看‘示例’以便得知符号的意义。比如:先选择‘百分比’然后马上选择‘自定义’会发现‘类型’中出现‘’这就是百分比的定义法把它改成小数位位的百分比显示法只要把‘’改成‘’就好了把它改成红色的百分比显示法只要把‘’改成‘红色’就好了。关于数据引用的问题在一个工作簿中假如A工作表中的单元格E被B工作表中的某单元格引用现在由于A工作表中插入了行原来的E现在可能是E,结果造成B工作表引用数据错误请问:如何可以使B工作表中的引用随着A表的变化也作相应的自动调整? 回答:利用“相对引用”来实现“相对引用”是Excel中默认的引用方式例:在工作表Sheet中C2单元格为5D2单元格为6在工作表Sheet中C3单元格中输入“=Sheet!CSheet!D”如果把C2单元格剪切到C3那么在工作表Sheet中C3单元格中的公式就自动变为“=Sheet!CSheet!D”如何使EXCEL应用程序锁定不让人打开请问我以下的操作应再如何修改成如密码输入错误则退出EXCEL这是我编写在PERSONALXLS中的一个模块代码如下,请各高手帮忙为小弟指点一二,不甚感激!SubautoOpen()MsgBox"热列欢迎来海源,你吃饱了吗?"IfApplicationInputBox("请输入操作权限密码:")=ThenElse'(在这一步中,我想退出EXCEL,但无法实现请高手指点一二)EndIfEndSub 解答:PrivateSubWorkbookOpen()MsgBox"热列欢迎来海源,你吃饱了吗?"IfApplicationInputBox("请输入操作权限密码:")=ThenExitSubElseApplicationQuitEndIfEndSub程香宙的修改:SubautoOpen()MsgBox"程香宙欢迎你的到来",vbQuestion,"联系电话:"IfApplicationInputBox("请输入操作权限密码:","系统登陆")=ThenElseMsgBox"密码错误,请重输",vbCriticalvbOKOnly,"你还有两次机会"IfApplicationInputBox("请输入操作权限密码:","系统登陆")=ThenElseMsgBox"密码错误,再给你一次机会!",vbCriticalvbOKOnly,"你还有一次机会"IfApplicationInputBox("请输入操作权限密码:")=ThenElseMsgBox"你无权进入本系统!请向程香宙申请密码!",vbCriticalvbOKOnly,"你没有机会啦!"ApplicationQuitEndIfEndIfEndIfEndSub数组的运算法则有excel表如下:姓名成绩王娟优永生中闵生刚优朱智锐中胡强强良金龙鳞优张正梅中汪欲生良闵生刚中王娟优张正梅优闵生刚中永生良王娟良其中姓名在sheet页的a列成绩在c列。我想在sheet页中建立一个表如下:姓名统计优数胡强强金龙鳞闵生刚汪欲生王娟永生张正梅朱智锐同样姓名在sheet的a列统计在c列这里的姓名已经整理为没有重复的姓名。要统计出每人获得优的数目。请问用什么函数能解决。我用了if((sheet!c)="优",countif(sheet!a:a,a)),结果是统计的姓名数而不是成绩数。如果能统计出来哪么就是一个动态的当sheet中数据变化时sheet中数据应到跟着变化。有位朋友指导采用下面的式子把上面的问题解决了。这里假设最大记录数为=sum((sheet!$a$:$a$=$a)*(sheet!$c$:$c$="优")*)按CtrlShiftEnter但是这是用到了数组运算请问数组运算的规则是什么看到许多地方都可以用数组解决但不知其所以然。帮助文件中也没说运算规则。如上式中为什么用*号?谢谢解答:对于数组公式的含义sum((sheet!$a$:$a$=$a)*(sheet!$c$:$c$="优")*)我们来一部分一部分的讲:、(sheet!$a$:$a$=$a)表示用sheet!$a$:$a$区域中的每一个单元格中的内容与$a单元格的内容进行比较如果相同结果为“True”否则为“False”。、(sheet!$c$:$c$="优")表示用sheet!$c$:$c$区域中的每一个单元格中的内容与字符串"优"进行比较如果相同结果为“True”否则为“False”。、最后一部分乘以。是强制Excel将“True”或“False”转换为数值“”或“”以便sum函数可以求和。、至于第一部分和第二部分之间的乘号(*)的目的是如果第一部分或者第二部分有一个的结果是“False”那么Excel将其转换为数值“”相乘结果为零表示不在求和范围内。替换数据请教各位如何用将一组数据如:我需要把数据中的数字换为符号A表示换为符号B表示依此类推将数据中的阿拉伯数字~分为几类用其它符号替换。解答:方法。假设:B值为。在B中输入=IF(ISERROR(FIND(MID($B$,,),"")),IF(ISERROR(FIND(MID($B$,,),"")),IF(ISERROR(FIND(MID($B$,,),"")),IF(ISERROR(FIND(MID($B$,,),"")),"","D"),"C"),"B"),"A")。 C中MID()第二个参数为以此类推最后在目标单元格中输入:=CONCATENATE(B,C,D,E,F,G,H)方法:表一:ABABABABCCCD=SUBSTITUTE(A,A$,A$)说明:先列一个替换表,如表一,在A处填如数据,在B处填如上述公式=SUBSTITUTE(A,A$,A$),并向右拖动个同样的公式,最后一个便是结果在将该个相同的公式向下拖,便得到其它的结果好处:可以修改表一,产生变化方法:你可以把全部数据拷贝到WORD中再用替换命令想怎么换就怎么换然后在拷贝回来。几个技巧用“Ctrl:”输入时间用“Ctrl”输入日期用“Ctrl`”显示当前工作表的单元格引用情况(如果引用的话)再次按下“Ctrl`”则回到正常的显示状态(别漏了那个点)“Ctrl”:打开“单元格格式”对话框按下ESC键关闭该对话框“Ctrl”:打开“删除”对话框按下ESC键则关闭该对话框热键ctrl:字体加粗或取消加粗ctrl:字体加斜或取消加斜ctrl:加下滑线或取消ctrl:加删除线或取消ctrl:隐藏当前行ctrl:隐藏当前列在Cell里输入公式比如=trunc按CTRLSHIFTA出现函数参数说明按CTRLA出现wizard。快速填充:选择要填充的单元输入公式或数值后按CTRLEnter。将图形与某个cell的数据联系在一起:a)选择绘图菜单栏的任一图形b)在公式栏里或按F输入到某个cell的联接比如=Ac)回车。粘贴链接图片:a)选择某区域(比如A:C)b)复制c)按住SHIFT点选"编辑>粘贴链接图片"。对长公式进行错误查找:点击公式按F出现出错的部分。ESC复原CTRLZ为undo。如何在两个工作表之间进行数据交换我有两个工作表第一个表A列是姓名B列是编号第二个表A列也是姓名但是顺序和第一个表的A列不一样我想在第二个表的B列也加入编号 。解答:用VLOOKUP函数:=VLOOKUP(A,Sheet!A:B,,FALSE)依次向下拖动显示数值所在的单元格号假如有A列和B列两列数字如何找出A列的数字在B列中所在的位置并在第三列显示单元格号。解答:假设数据在A:B则C列公式为:=MATCH(B,$A$:$A$,)我想根据题意应该为:=MATCH(A,$B$,)if 超过层如何办将七层之外的IF语句放在另外的单元格内来处理例:C=if(if,,(if),B))),B单元格就是存放七层之外的IF语句。依此类推可以实现在数据库语言中CASE语句的功能。IF 函数的确有七层嵌套的限制。遇到七层嵌套还解决不了的问题可以尝试用其它的函数组合和数组公式来解决有时用 VBA 方案可以有很好的效果。这里给出一个解决IF函数嵌套超出范围的方法可能比较容易使初学者看懂。其思路是:一个单元格做不了的事分给两个或更多的单元格来做文字内容是这样函数内容也是这样。例子:假如 A=则 B=AA=则 B=B …… A=则 B=Z解决方法如下:B = IF(A=,"A",IF(A=,"B",IF(A=,"C",IF(A=,"D",IF(A=,"E",IF(A=,"F",IF(A=,"G",IF(A=,"H",C))))))))C = IF(A=,"I",IF(A=,"J",IF(A=,"K",IF(A=,"L",IF(A=,"M",IF(A=,"N",IF(A=,"O",IF(A=,"P",D))))))))D = IF(A=,"Q",IF(A=,"R",IF(A=,"S",IF(A=,"T",IF(A=,"U",IF(A=,"V",IF(A=,"W",IF(A=,"X",E))))))))E = IF(A=,"Y",IF(A=,"Z","超出范围"))根据情况可以将 C、D、E 这些从事辅助运算的单元格放在其它任何地方一个单元格也可以实现=IF(A=,"A",IF(A=,"B",IF(A=,"C",IF(A=,"D",IF(A=,"E",IF(A=,"F",IF(A=,"G",IF(A=,"H",""))))))))IF(A=,"I",IF(A=,"J",IF(A=,"K",IF(A=,"L",IF(A=,"M",IF(A=,"N",IF(A=,"O",IF(A=,"P",""))))))))IF(A=,"Q",IF(A=,"R",IF(A=,"S",IF(A=,"T",IF(A=,"U",IF(A=,"V",IF(A=,"W",IF(A=,"X",""))))))))IF(A=,"Y",IF(A=,"Z",""))(数组形式输入)。以一例:a=,,,,,,,,,b=if(a=,"一",if(a=,"二",if(a="三",if(a=,"九",if(a=,"十")))))),if超过层不起作用我该如何办解答:、b=if(a>,if(a=,"六"。。。。。。))明白意思?就是截为两段再做判断这样可以不超过重。、可以用自定义数字格式。也可以用=CHOOSE(A,"一二三四五六七八九十")问:实际上我的要求是现行高一成绩统计中:b=if(a="语文","语文",if(a="数学","数学",if(a="英语","英语",if(a="物理","物理",if(a="化学","化学",if(a="历史","历史",if(a="政治",政治",if(a="生物","生物“if(a="地理","地理")))))))),这样超过了层。我不知如何处理。因为下面的公式要引用语、数、英、等。答:新建一表取名Data找一区域设置名称为SubjectTable:语文Chinese英语English=vlookup(SubjectTable,a,,false)可以有个够了吧。其实稍加改进理论上可以有达到你硬盘空间的个数。或用if和or的组合可以解决个。再举个例子:=IF(A="","",IF(B="","样办尚未交",IF(OR(B="内部检查中",B="数据查询中",B="数据查询中"),CONCATENATE(IF(B="内部检查中","品质检测中",""),IF(B="数据查询中","图纸未确认",""),IF(B="为不合格","需要修正","")),CONCATENATE(IF(C="客户检查中","待客回复",""),IF(C="合格","待PO生产",""),IF(C="取消","客户取消",""),IF(C="为客户设变中","客户设变中",""),IF(C="不合格","需要修正","")))))一个单元格内格式问题如果我做了一个表某一列是表示重量的数值很多在之间的数不等。这些表示重量的数。如果我想次给他们加上单位但要求是单位是>吨之下>是千克其余的是克。如何办答:>###,"吨"*,*"千克"怎样用函数求出一定范围内的数值的偶数的个数解答:设你的数据区域为A:A{=COUNT(IF(A:A=INT(A:A),A:A))}如果你的数据区域内还包括空白格你不想计算在内的话{=COUNT(IF(A:A=INT(A:A),A:A))COUNTBLANK(A:A)}或:{=sum((even(a:a)=a:a)*)}如何使某列的数据不重复、我做了个宏可惜在数据量大时(超过时)速度狂忙各位帮我修改修改Sub检查重复项()DimiAsIntegerDimjAsIntegernum=SelectionCellsCountFori=TonumForj=iTonumIf(SelectionCells(i)=SelectionCells(j))ThenMsgBoxSelectionCells(i)ValueEndIfNextjNextiMsgBox"检索完毕"EndSub、假设数据在B列选中B列数据数据有效性自定义在“公式”输入框中键入:=COUNTIF(B:B,B)=。请问如何能使随机数不重复我在个单元格里使用了randbetween(,),随机抽取了之间的任意数,但问题是这个数中仍有机会出现重复请问如何才能使这些随机数不重复呢答:、勾选迭代计算A输入如下公式:=IF(COUNTIF($A$:$E$,A)=,A,RANDBETWEEN(,))如果某格出现值点选旁边单元格拖一下即可。、用上面的方法得到的随机数不会变化如何做到想变就变?增加一个条件语句即可达到此效果公式如下:=IF(COUNTIF($A$:$E$,A)=,IF($G$=,RANDBETWEEN(,),A),RANDBETWEEN(,))G为控制格当在G输入数字就开始变。变化后的数据有可能不惟一了怎么办?把清除即可!日期型编号想在单元格里输入产生这样一个数字是年是月份是日是输入的数值答:如a输入要在b生成你要的数字你可以在b设公式=text(today(),"yymmdd")a如何将数字改变为字符串答:=TEXT(A,"")在Excel中如何自定义数字格式虽然Excel为用户提供了大量的数字格式但还是有许多用户因为工作、学习方面的特殊要求需要使用一些Excel未提供的数字格式这时我们就需要利用Excel的自定义数字格式功能来帮助实现用户的这些特殊要求。一、在Excel中创建自定义数字格式的方法、选择要设置格式的单元格或单元格区域。  、单击“格式”菜单中的“单元格”命令然后单击“数字”选项卡。  、在“分类”列表中单击“自定义”选项。  、在“类型”框中编辑数字格式代码以创建所需的格式。在Excel自定义数字格式使用如下的表达方式:  正数的格式   负数的格式   零的格式   文本的格式    #,##   Red#,##      "TEXT"在Excel自定义数字格式的格式代码中用户最多可以指定四个节每个节之间用分号进行分隔这四个节顺序定义了格式中的正数、负数、零和文本。如果用户在表达方式中只指定两个节则第一部分用于表示正数和零第二部分用于表示负数。如果用户在表达方式中只指定了一个节那么所有数字都会使用该格式。如果在表达方式中要跳过某一节则对该节仅使用分号即可。二、自定义数字格式实例(一)自动添加文本在日常财务工作中常常需要在金额数字后加单位“元”这时就可以使用“元”的自定义数字格式当用户在单元格中录入数字后Excel就会自动在数字后加上单位“元”。如果需要将单元格中录入的数字“”自动转换成“高三十二班”格式只要将单元格的数字格式定义为“高DBNumG通用格式班”即可。(二)在自定义数字格式中使用颜色要设置格式中某一部分的颜色只要在该部分对应位置用方括号键入颜色名称或颜色编号即可。Excel中可以使用的颜色名称有黑色、蓝色、青色、绿色、洋红、红色、白色、黄色八种不同的颜色此外Excel还可以使用颜色X的方式来设置颜色其中X为之间的数字代表了种不同的颜色。例如:当用户需要将单元格中的负数数字用蓝色来表示只要使用“#,##蓝色#,##”自定义数字格式用户在单元格中录入负数时Excel就会将数字以蓝色显示。(三)在自定义数字格式中使用条件格式在Excel自定义数字格式中用户可以进行条件格式的设置。当单元格中数字满足指定的条件时Excel可以自动将条件格式应用于单元格。Excel自定义数字格式中可以使用如下六种标准的比较运算符:   运算符    含义     =      等于     >      大于     <      小于     >=      大于等于     <=      小于等于     <>      不等于在Excel中要想设置满足指定条件数字的格式在自定义数字格式代码中必须加入带中括号的条件条件由比较运算符和数值两部分组成。例如:在学生成绩工作表中当我们想以红色字体显示大于等于分的成绩以蓝色字体显示小于分的成绩时其余的成绩则以黑色字体显示这时只需将自定义数字格式设置为“红色>=蓝色<黑色”即可。值得注意的是当你在以后需要继续使用刚才所创建的成绩条件自定义数字格式时你会发现在“单元格格式”的“自定义”分类类型中找不到“红色>=蓝色<黑色”格式这是因为Excel自动将你所创建的“红色>=蓝色<”格式修改成“红色>=G通用格式蓝色<G通用格式黑色G通用格式”你只需选择此格式即可达到同样的使用效果。(四)隐藏单元格中的数值在Excel工作表中有时为了表格的美观或者别的因素我们希望将单元格中的数值隐藏起来这时我们使用“”(三个分号)的自定义数字格式就可达到此目的。这样单元格中的值只会在编辑栏出现并且被隐藏单元格中的数值还不会被打印出来,但是该单元格中的数值可以被其它单元格正常引用。在单元格显示负数为红字后又紧跟汉字解答:、这个使用宏可以解决但单元格开始请不要带单位。单个单元格宏代码如下:Sub宏()''宏Macro'Dent记录的宏'DimstrAsStringstr=Str(ActiveCellValue)IfVal(str)<Thenstr=Abs(Val(str))"文字"ActiveCellValue=strActiveCellFontColor=vbRedEndIfEndSub、用单元格格式解决:#,##红色#,##"文字"检索问题在一个工作表中有客户名称、收款日期等字段名及相应数据。现在想为了检验收款日期是否到期要求在工作表的某一空白单元格中键入某一日期则此单元格以下的单元格可以显示收款日期字段中所有在此日期前的日期清单请问如何实现?并要求不使用筛选功能。解答:Sheet!A命名为X,键入日期Sheet:客户名称、收款日期等字段名及相应数据插入名称定义:XX=OFFSET(Sheet!$A$,,,MATCH(X,Sheet!$B$:$B$),)XX即是您所需要之范围vba写在ThisWorkbookPrivateSubWorkbookSheetChange(ByValShAsObject,ByValTargetAsExcelRange)OnErrorGoTo:TargetSelectRange(TargetValue)CopyActiveSheetPasteLink:=FalseApplicationCutCopyMode=FalseEndSub**********************************************************************************Sheet任意储存格键入xx按ENTER比较一下并没有比筛选功能快,反而复杂SHEET随便贴张图键入公式=XX微软到底对我们隐藏了多少NumberString 这个函数是否一直存在但没有解释?NumberString(,)就像自定格式DBNum一样它还有的选择。其实这些特别用途的函数是否中文板设计时特别隐藏。测试结果:NumberString(,)=一十二亿三千四百五十六万七千八百九十NumberString(,)=壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾NumberString(,)=一二三四五六七八九○DateString()相信亦是一特别Lotus函数。DATESTRING("Sep")=年月日DATESTRING("Sep")=年月日DATESTRING("")=年月日DATESTRING("")=年月日求一组数中第一个出现的正数单元格AG中存放一组数如何使A的值等于这组数中第一个出现的正数(按AG的顺序)若无正数则A=解答:=IF(A<=,IF(B<=,IF(C<=,IF(D<=,IF(E<=,IF(F<=,IF(G<=,,G),F),E),D),C),B),A)或:{=IF(COUNTIF(A:G,">")=,,INDIRECT(ADDRESS(,SMALL(IF(A:G>,COLUMN(A:G)),))))}能否使表格不能打印解答:PrivateSubWorkbookBeforePrint(CancelAsBoolean)Cancel=TrueEndSub(用法:打开Excel,按AltF,调出VBE后,按Ctrlr显示出工程资源管理器(默认此窗口应已显示),双击ThisWorkbook,在模块中粘入代码即可!要想打印你表,用截图软件即可无最保险办法)。有无这样的函数A为一定范围内的随机值BB为一组数有无这样的函数来确定A的值?当A=时A=B当A=时A=B当A=时A=B…………解答:=INDIRECT(ADDRESS(A,))(绝)能实现两栏数据中按特定条件进行比较并做出相应记号吗具体意思如下:有四栏数据。A栏B栏C栏D栏F栏对A栏及C栏数据进行查找对比如果发现存在A=C且B=D则在符合条件的F栏显示已找到对于没有查到的的显示未找到。解答:如果是一一对应查找则f=if(and(A=C,B=D),"OK","No"),复制如果整体查找:=IF(AND(COUNTIF($C$:$C$,A)>,COUNTIF($D$:$D$,B)>),"找到","没有")如何对文本格式的数字进行筛选如……解答:要对A列进行筛选条件:符号""左边的数字>=条件:符号""右边的数字>=,达到任一条件的即被选中。试试这个公式:B=IF(OR(LEFT(A,FIND("",A))*>=,MID(A,FIND("",A),LEN(A)FIND("",A))*>=),A,"")我想再请教一个问题:在自动筛选中有"包含、不包含"之类的选项,这类选项在高级筛选中能否实现提供以往一个函数公式,请你研究看是否有参考:={SUM((ISNUMBER(SEARCH("广东",$B$:$B$)))*($A$:$A$="股份制")*($C$:$C$))}这是对B列单位名称中包含"广东"并且A列企业类型为"股份制"的,按C列汇总如何筛选出特定行一份工作表有几万行数据现在想每隔行打印一行如何进行筛选解答:插入一空列,输入=MOD(ROW(),)并复制到全数据列,再用筛选法把有“”的单元格选出Word加启动密码在Word模板中录制如下宏文件保存即可:Subautoexec()Dimuseruser=InputBox("请输入软件启动密码")Ifuser="password"ThenElseMsgBox("非法用户,将退出本程序Wu")ApplicationQuitEndIfEndSub有关大小写的转换在EXCEL单元格中有的单元格是小写字母有的是大小写混在一起现在我想全部转为大写、或全部转为小写有哪些方法呢?利用upper()函数或还有LOWER。如:a=aBcDupper(a)=ABCD在Excel中打造自己的“公式保护”菜单工作中经常用到Excel的计算功能有时一张工作表中需要设置很多公式为了防止误操作就将工作表保护起来但是如果将含有公式的单元格保护而其它单元格不锁定设置时需要一个个单元格选定很麻烦而且容易出错就想到建立一个菜单项将这项工作变成菜单。有幸的是这一切Excel都能完成具体操作如下:先将工作表保护不必输入密码。再录制一个宏。方法是:打开“工具”栏单击“宏”“录制新宏”将其命名为“公式保护”单击“保存在”的下拉箭头将新宏保存到“个人宏工作簿”。单击“确定”录制开始。(注意此后的每一动作都将被录制直至“停止录制”。)将工作表取消保护选定a单元格输入="测试"(注:黑体为实际输入内容标点符号均为英文输入法状态,下同)单击工作表左上角的方框选定整个工作表单击“格式”“单元格”“保护”项将“锁定”和“隐藏”前的方框里的对号去掉单击“插入”“名称”“定义”当前工作表名称输入:公式保护引用位置输入:=getcell(,indirect("rc",false)),单击“添加”后“确定”单击“格式”“条件格式”在“单元格数值”下拉框选定“公式”后面的框内输入=公式保护单击“格式…”在图案标签内选浅绿色或者其它颜色“确定”再“确定”单击“编辑”“定位”“定位条件…”选定“公式”项“确定”再“确定”重复步骤将“锁定”和“隐藏”前的方框里的对号选定选定a单元格取消其内容并重复步骤取消a的锁定和隐藏属性单击工具保护保护工作表将工作表保护(也不必设密码)。再单击“工具”“宏”“停止录制”停止录制。再打开“工具”“自定义”项再“命令”标签栏内“类别”栏中找到“新菜单”并指定在其右侧的命令框中的新菜单用鼠标按住拖到“工具栏”的“保护”项中的“保护工作表”下面放开并单击右键将其命名为“公式保护”将刚才录制名为“公式保护“的宏指定给它。至此一个很有用的菜单项就作成了。此后只要你将鼠标移动到“工具”“保护”“公式保护”的位置工作表将执行其所指定的宏只要你工作表中输入了公式(以=开头)含有公式的单元格将自动变为浅绿色提醒你和别人此处有公式小心编辑十分醒目。编辑完公式后再次运行该命令就可以此保护工作表并锁定公式禁止改动。如何让EXCEL自动从头统计到当前单元格情况如下:C列要根据A列的内容来统计B列的数据范围从A:An即当A列中An有数据时Cn自动根据An的值统计B:Bn的数据。解答:=SUM(INDIRECT("B:B"LARGE((A:A<>"")*(ROW(A:A)),)))按CtrlShiftEnter。请问想要取当前单元格的列号用什么函数、=CHAR(COLUMN())、PrivateSubWorksheetSelectionChange(ByValTargetAsExcelRange)IfSelectionColumnsColumn>Thentt=Mid(ActiveCellAddress,,)Elsett=Mid(ActiveCellAddress,,)EndIfMsgBox(tt)EndSub、的公式就变化一下:=IF(COLUMN()>,CHAR(INT(COLUMN()))CHAR(MOD(COLUMN(),)),CHAR(COLUMN()))SUMIF函数a至A是,,,B至B是,,,D至D是,,,用SUMIF第一参数选取A:B,第二参数是>,第三参数选取D:D它得出是。它判断的是A:A。我的要求是B:B也跟着判断,也就要得出来得是解答:{=SUM((A:A>)*(B:B>)*C:C)}怎么能快速的将两个单元格的内容互换、SubChangVal()myvalue=ActiveCellValueForEachaInSelectionIfaAddress<>ActiveCellAddressThenmyvalue=aValueaValue=myvalueActiveCellValue=myvalueEndIfNextaEndSub、用鼠标先选定单元格点住单元格边框并按住SHIFT键然后托拽到隔壁单元格的后面一条边框处。你能看到被托拽部分会变成灰色的“工”字形然后放手后单元格就互换了。如何能到两个时间段的::小时数已知"E"为(为文本格式﹐开始时间﹐意思为月日下午﹕)﹐"G"为(为文本格式﹐结束时间﹐意思为月日上午点)问﹕如何能得到开始时间到结束时间(::)的总小时数如何得到开始时间到结束时间的节假日时数(如﹐﹐星期六﹐星期天)解答:方法:=(DATE(YEAR(NOW()),MIDB(AF,,),MIDB(AF,,))DATE(YEAR(NOW()),MIDB(E,,),MIDB(E,,)))*IF(MIDB(E,,)MIDB(E,,)<=,(MIDB(E,,)MIDB(E,,)),IF(MIDB(E,,)MIDB(E,,)>=,(MIDB(E,,)MIDB(E,,)),))IF(MIDB(AF,,)MIDB(AF,,)<=,MIDB(AF,,)MIDB(AF,,),IF(MIDB(AF,,)MIDB(AF,,)>=,MIDB(AF,,)MIDB(AF,,),))方法:networkdays需要你提供一个节假日列表作为参数。我猜你不会喜欢手工输入这样一个表。以下公式功能更为强劲它可以算出两个单元格(A和C)所储存的日期之间的周末天数乘以小时数即可计算出你所说的节假日时数。但是它无法计算国庆节等公众假期。=IF(C>A,SUM(IF(WEEKDAY(CROW(INDIRECT(":"CA)),)>,,)),SUM(IF(WEEKDAY(AROW(INDIRECT(":"AC)),)>,,)))。这是一个数组公式输入完成后按CTRLSHIFTENTER结束。方法:用格式定义E,G为日期时间格式:**********:**。然后:(GE)*即可得两时间内的小时数。如何在单元格返回工作表名称答:=RIGHT(CELL("filename"),LEN(CELL("filename"))FIND("",CELL("filename")))如何在输入数字的加减乘除按ENTE后能在另一单元格自动出现计算数值、编了个宏:Subaa()Cells(,)=Mid(ActiveCellFormula,,)EndSub先在单元格里输入公式得出结果,选定得出结果的单元格运行宏就可以在A出文本 、宏(测试通过)Subaa()A='行'B='列LINE:IfCells(A,B)=""ThenExitSubElseCells(A,B)Formula="="Cells(A,B)A=AGoToLINE:EndIfEndSub有ABCD四个单元格D的值要随着A1的变化而变化A1有三种变化一、二、三当A1=‘一’时D1=“ ” A1=‘二’时D1=(B1+C1)/3 A1=‘二’时D1=(B1+C1)/6。以上可以通过IF来完成。可我希望的是:当B1或C1为空时不论A1为什么D1都为空这样做得到么?解答:、=IF(OR(B="",C=""),"",IF(A="一","",IF(A="二",(BC),IF(A="三",(BC),""))))、=IF(OR(B="",C="",A="一"),"",IF(A="二",(BC),(BC)))、如果考虑A没有数据的话:=IF(OR(B="",C="",A="一",A=""),"",IF(A="二",(BC),(BC)))对A列不重复的数值计数我只能做到新建一列B列然后第一个单元格countif($A$:$A$,A)然后拖动到全部新列。最后在新列下面用sumif(B:B,)谁有更好地方法。解答:、{=SUM(IF($A$:$A$="","",(COUNTIF($A$:$A$,$A$:$A$))))}(又问:公式中的“(COUNTIF($A$:$A$,$A$:$A$))”像是一个倒数怎么理解?答:用倒数是这个意思:如果只出现一次数组中的相应项统计为其倒数为Sum统计计。如果出现N次其倒数为N出现了N次求和就是NxN最后Sum统计就只计。)永恒的求和、=SUM(OFFSET(A,,,ROW()ROW(A)))可以对A列数值自动求和。、=SUM(INDIRECT("RC:RC",FALSE))、=SUM(INDIRECT("A:A"ROW()))、坚持不用RC栏名列号表示法还有一个方法不过又复杂了些。首先定义一个公式:COL=IF(COLUMN()>,CHAR(INT((COLUMN())))CHAR(IF(MOD(COLUMN(),)=,,MOD(COLUMN(),))),CHAR(COLUMN()))。后于欲求加总之储存格输入:=SUM(INDIRECT(COL":"COLROW()))。则此公式复制到任何一任皆可用又不怕产生错误值。(注:COL=IF(COLUMN()>,CHAR(MOD(COLUMN(),))CHAR(INT(COLUMN())),CHAR(COLUMN()))。暴露了一个为人不知的缺点如果列数到了AA列以后就不行了虽然可用ADDRESS()解决比用CHAR()好多了但公式还是太长用在一个加法中实在不值)、其实用ADDRESS更好=SUM(INDIRECT(ADDRESS(,COLUMN())":"ADDRESS(ROW(),COLUMN())))还是一句老话为做一个加法不是太值这只能是技术上的讨论。如何使用EXCEL进行动态跨表取数有两个文件第一个文件有张日报表每天一个表单第二个文件仅一个表单如何在第二个文件中输入时由函数动态取出第一张表单数(如SHEET)输入时取(SHEET)数依次类推……。如何设公式。在同一文件中可以用INDIRECT和ADDRESS组合可是跨表好象不行请各位指教一二!解答:前提是两个工作表都要打开:=INDIRECT("BookSheet"A"!$B$")如何使用對照表设一对照表如下:如何使用函数当输入。当a=则a显示"乙"b=则b显示"丁"甲乙丙丁戊己更辛解答:如A列输入的数字在表中都存在把你的数据放在D到G列甲乙丙丁戊己辛放在最后一列即H列。在B列输入公式“=IF(A="","",VLOOKUP(A,INDIRECT(ADDRESS(,INT(*A)IF(A>,,))):INDIRECT(ADDRESS(,)),INT(*A)IF(A>,,),FALSE))”。如甲乙丙丁戊己辛放在第一列即列用公式“=IF(A="","",INDEX(D:D,MATCH(A,INDIRECT(ADDRESS(,INT(*A)IF(A>,,))):INDIRECT(ADDRESS(,INT(*A)IF(A>,,))),)))”解答:如果数据无规律可用以下方法:如你的数据表在D到H列在I列输入公式“=IF(ISERROR(MATCH(A$,E:H,))=TRUE,,row())"并向下填充在B单元格输入公式“=IF(A="","",INDEX(D:D,LARGE(I:I,)))”解答:我将解答公式做了小修改(I列公式删除)B单元格输入公式:=IF(A="","",INDEX($D:$D,IF(ISERROR(MATCH(A,$E$:$H$,))=TRUE,,ROW($E$:$H$))IF(ISERROR(MATCH(A,$E$:$H$,))=TRUE,,ROW($E$:$H$))IF(ISERROR(MATCH(A,$E$:$H$,))=TRUE,,ROW($E$:$H$))IF(ISERROR(MATCH(A,$E$:$H$,))=TRUE,,ROW($E$:$H$))IF(ISERROR(MATCH(A,$E$:$H$,))=TRUE,,ROW($E$:$H$))IF(ISERROR(MATCH(A,$E$:$H$,))=TRUE,,ROW($E$:$H$))IF(ISERROR(MATCH(A,$E$:$H$,))=TRUE,,ROW($E$:$H$))IF(ISERROR(MATCH(A,$E$:$H$,))=TRUE,,ROW($E$:$H$))))。解答:在B单元格输入公式:{=IF(COUNTIF($E$:$G$,A)=,"查无资料",INDEX($D$:$D$,SUM(IF($E$:$H$=A,ROW($E$:$H$)))))}又问:当数据区域有重复数据时就得不到正确结果。因为在这里你的SUM()返回的只是对一个数据求和。如果有重复数据怎样才能得到正确结果呢?答:在B单元格输入公式:=IF((COUNTIF($E$:$H$,$A$)=)(COUNTIF($E$:$H$,$A$)<ROW()),"",INDEX($D$:$D$,SMALL(IF($E$:$H$=$A$,ROW($E$:$H$)),ROW())))再往下拖曳就可依序顯示了。真是快瘋了改了十幾次有些莫名其妙進來編輯看公式是完整的可是發表後又老是缺東缺西的只好多加些空白或強迫分段處理請使用者自行修改。或:如有重复数据,則顯示"数据重复"表示,代表要修改数据。{=IF(COUNTIF($E$:$H$,A)=,"查無資料",IF(COUNTIF($E$:$H$,A)>,"資料重複",INDEX($D$:$D$,SUM(IF($E$:$H$=A,ROW($E$:$H$))))))}如何在单元格返回工作表名称解答:=RIGHT(CELL("filename"),LEN(CELL("filename"))FIND("",CELL("filename")))何在一列数据中统计限定范围的数据请教各位:现A列有数字(包括绝对值为的数字)、文本、空格,要统计数值为到(包括与)的个数还要统计数值>并<的个数(不包括),分别该用什么函数答:>and<公式=COUNTIF(A:A,">")COUNTIF(A:A,">")(>or<)and<>公式=COUNTIF(A:A,">")COUNTIF(A:A,"<")COUNTIF(A:A,"=")V

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/70

EXCEL使用技巧集合(目前见到最实用的)Excel问题集合

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利