ITPub博客

首页 > 应用开发 > IT综合 > EXCEL问题集合5

EXCEL问题集合5

原创 IT综合 作者:jackdear 时间:2008-01-14 00:54:22 0 删除 编辑
接 :EXCEL问题集合4[@more@]

如何定义有效数字

例:取两位有效数是从第一个不是零的数字起,取两位。0.0023666取两位有效数是0.0023 0.2366取两位有效数是0.23解答:用函数可如下: =FLOOR(A1, SIGN(A1)*10^(INT(LOG(ABS(A1)))-1)), +/- 小数有效,0无效.其它形式的数据, 自行扩展.

sheet1工作表的A1A2A3单元格分别链接到sheet2sheet3sheet4

解答:1 =indirect("sheet"&row()+1&"!a1")《程香宙的解释:indirect是把文本变为单元格引用的函数row()是取当前行号。例如在a1输入该公式,则row()=1,公式里的值变为indirect("sheet2!a1"),跟=sheet2!a1同效,在a2输入该公式,则row()=2,公式里的值变为indirect("sheet3!a1")
2
使用插入----超级链接----书签----(选择)----确定

SUMIF函数进行条件求和,不限于一个条件时如何设置参数

例如:有一个表格登记面粉、米粉、糯米、梗米、绿豆、早米……等等的进出流水帐,如果对满足单一条件的如面粉、糯米、绿豆等分别求和是没有问题的,但如果要将同一类的求和,例如将糯米、梗米、早米的数值加在一起,应该怎么办? 解答:提供以下公式供参考,设A列为名称、B列为数量:
=SUMIF(A:A,"
糯米",B:B)+SUMIF(A:A,"梗米",B:B)+SUMIF(A:A,"早米",B:B)B1:D1为求和条件项,即B1="糯米",C1="梗米",D1="早米",上述公式还可改为:
=SUMIF(A:A,B1,B:B)+SUMIF(A:A,C1,B:B)+SUMIF(A:A,D1,B:B)

如何在excel中已有的数值前加零变成六位

比如说 253691569等,操作后变成00002500036900156解答:如果直接输入的话,可以在数值前面加“'”,如“'002020”;

如果处理现成的数据,或者从别处(比如从A1单元格)链接来的数据,可以用公式:RIGHT("00000"&A1,6)

如何提取工作表中的背景图片

解答:找个干净的地方, 去掉网纹等不需要的东西, PrintScreen 再编辑

绘制有三条斜线的表头

解答:1用绘图工具画出斜线>>画方框>>内添加文字>>去边框
2
、引用WORD中的,然后再复制过来就可以!

A列有一组数据不是按照大小顺序排列在B列中排名

解答:方法1、将ACOPYB列,再排序。
2
rank函数(=RANK(A2:A11,$A$2:$A$11,0)(假设数据在A2A11单元格,下同)
3
、使用contif函数进行排列“=countif(a$2:a$11,">"&a2)+1"

有无办法让B2所在行都呈红色字体

解答:假设你有一个B列和一个A1的值,你的目的是,如果B2A1的话,整个B列都为红色显示!设置如下:先选定整个b列,也就是在B列列标处单击(废话~^_^),选择格式-条件格式出现条件格式对话框,单击左边的下拉列表,里面只有两项,单元格数值和公式,选中公式,右边就可以输入任何可以返回逻辑值的公式了。输入这个公式=($B$2=$A$1)。千万注意要用绝对引用,因为如果是相对的,excel又自作主张的一个一个判断了,就没有作用了。(绝对正确并且好用)

现有12个工作表,12张发票,建立一个汇总表,将发票号和金额汇总显示在一张表里

(发票号和金额在每张表的相同位置).解答:在A1输入 =INDIRECT("sheet"&ROW()&"!d3")B1输入 =INDIRECT("sheet"&ROW()&"!d10")再选择A1B1往下复制到第12行。

经验技巧

按“Ctrl+~”可以一次显示所有公式(而不是计算结果)。再按一次回到计算结果。(程香宙)

在一个不对称的区域中如(b1:G7)中找到A行一组数据中的某个数并自动变红

解答:其实也很简单,你只要选定你的b1:g7,设置它的条件格式为=(COUNTIF($A$1:$A$7,b1))注意,b1为相对引用,这里输入所选区域的第一个取值,那样你的所选区域会自动填充.达到你要的效果。()

不借助第三列而直接用函数或公式一步得到sum(a2/b2,a3/b3,)的结果

解答:输入=sum(a1:a100/b1:b100),按ctrl+shift+Enter

请问要如何算出每个月有几个星期一、二、三….

解答:为简单起见,表格需作一下调整, "星期日" 移到 C1,其后依次,这也符合规则(请参阅函数: WEEKDAY()). 。在 C2 键入数组公式: {=SUM(IF(WEEKDAY(DATE($A2,$B2,ROW(INDIRECT("$A$1:$A$" & DAY(DATE($A2,$B2+1,1)-1)))))=COLUMN()-2,1))},向右复制、向下复制。公式解释一点:ROW(INDIRECT("$A$1:$A$" & DAY(DATE($A2,$B2+1,1)-1)))实际上是从 1 号测试到本月的最后一天.如需要,公式可再作精简。

让隐藏的列或行不参预计算

解答:使用subtotal函数,详细用法参见帮助。

一次删完Excel里面多出很多的空白行

解答:1、用分面预览看看
2
、用自动筛选然后删除
3
、用自动筛选,选择一列用非空白,空白行就看不到了,打印也不会打出来。但是实际上还是在的,不算删除。或者用自动筛选选择空白将空白行全显出来一次删完也可以。
4
、先插入一列,在这一列中输入自然数序列,然后以任一列排序,排序完后删除数据后面的空行,再以刚才输入的一列排序,排序后删除刚才插入的一列。

1、表2分别有20个人的基本情况和其中10个人的名字,让表1的数据自动填充到表2

答:1、用lookup函数即可。要保证20人不重名;
2
、假设表1D列对应表2E列。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在格式---工作表----取消隐藏是看不见的。问:方法2更好哦,如何恢复呢?答:sheet1.Visible =xlSheetVisible

工具菜单与视图中的工具栏不同

屏蔽工具菜单宏
sub notool()
MenuBars(xlWorksheet).Menus("
工具").Delete
end sub
解除屏蔽
sub yestool()
MenuBars(xlWorksheet).reset
end sub
Alt+F11
进入VBA 编辑、插入模块、将上面宏复制到模块、运行宏。OK

查找并填写符合条件的单元格内容

我在工作中需快速复制每行多个数据(单元格)中最小值所对应的“标题名”,如E6C6Y6中的最小值,所对应的标题是E5单元格“某某公司”,要将其(某某公司)复制到B6单元格中,以此类推的复制很多很多行的内容。如果是手工一个一个查找与复制,实在是太慢太笨了,能否使用一个简单的公式计算呢?答:B6单元格"=INDEX(C$5:Y$5,MATCH(MIN(C6:Y6),C6:Y6,0))" 

填写空白行

我有个同事在一张空白表依次输入数据,为了省事她把和上一格内容相同的的省略不输,输了近200行。后来又觉得不够正式,想把空白的地方补上。她来问我怎么办好。当然依次填充也行,但我觉得烦(如果有2000行怎么办呵呵)我想了一个不是办法的办法:在A列和B列旁各插入一列,现在就有ABCDE列,我在B2中复制了A2中的内容,然后在B3中输入公式:IF(A3=0,B2,A3),然后往上往下复制公式。这样就填满了。如法炮制D列后隐藏AB列感觉就可以了。可是也烦啊,谁有更好的办法?答:1Sub feifjeifjeifjeifjeifjiefjiejfiejf()
For i = 2 To ActiveSheet.Range("a1").CurrentRegion.Rows.Count
If IsEmpty(Cells(i, 1)) Then
Cells(i, 1).FormulaR1C1 = Cells(i - 1, 1).Value
End If
Next
End Sub
2
筛选出空白行,输入公式=INDIRECT("a"&ROW()-1),填充

制订下月计划并显示为中文

我在五月份做六月份的计划,为减少工作量和更改的麻烦,我做模板并使用了公式="计划期:"&YEAR(NOW())&""&(MONTH(NOW())+1)&"",结果如A1所示 计划期:20026月(现在的系统日期是20025月)。 如果我想自动得到如A2中的结果 计划期:二○○年六月 ,请问要如何做才行,我设置了单元格的日期格式还是不行。解答:1、先设置单元格格式为"二○○二年六月"那种类型,然后用如下公式:=DATE(YEAR(NOW()),(MONTH(NOW())+1),20)就可以了。
2
、使用这个函数吧! =EDATE(NOW(),1)。单元格格式应设置为:日期----一九九七年三月

3
设置单元格格式为:[DBNum1]"计划期:"yyyy""m"" ,然后直接输入日期值(2002/11)即可。输入公式也可以。如=today()+30,可以得到下个月的月份。

&的用法

E44单元格,我希望 总计:=SUM(E45:E49) 就是想让它经过自动求和后在一个单元格内显示总计:120 解答:有多种方法实现,详细如下:
1
="总计:"&sum(e45:e49)
2
E44格式设为"总计:"#0.00;"总计:"-#0.00;"总计:"0.00;@
3
E44单元格格式自定义为 "总计:"0.000 即可,方便对E44的引用计算
4
=CONCATENATE("合计:",SUM(e45:e49))

5行数据在每行上面个插入1

解答:1在最左边插入一列,然后输入1234512345,并以此列进行排序,在第一行上面再插入一行,删除刚刚插入的列。
2
、使用Ctrl+鼠标一行一行选定,然后插入行。
3
sub 插入行()

for i=1 to 6
if cells(i,1).value <> Cells(i + 1, 1) And Cells(i, 1) <> "") Then
Rows(i + 1).Insert
end if
next i
end sub

可以检查一张表里是否有漏重的数字吗

答:漏值:{=IF(SUM((R1C1:R10C4="")*1)>0,"有漏值","无漏值")}重复值:{=SUM(SUM((漏值!R1C1:R10C4=漏值!RC)*1))}
{=IF(MAX(R1C1:R10C4)>1,"
有重复值","无重复值")}使用下面公式更方便:找重复值-------{=IF(SUM((COUNTIF(R1C1:R10C4,R1C1:R10C4)>1)*1)>1,"有重复值","无重复值")}找 漏 值-------{=IF(SUM((R1C1:R10C4="")*1)>0,"有漏值","无漏值")}注意:这两个公式均为数组,输入时应同时按Ctrl+Shift+Enter

怎样将单元格中的公式转换为数值

解答:选中公式的一部分,按F9

条件求和

有这样一个表格

A    B     C

1 2 3 /2 2 3 /3 2 3 /4 2 3 /5 2 3/6 2 3 /如何才能求出满足A列中大于2且小于5B列和C列数值的和,要求B列和C列的值相加。用sumif函数似乎条件中只能设定为>2,而不能同时设定<5,而且在求和时只能B列相加,不能把B列和C列满足条件的值加起来。这个问题能不能只用函数,不用数组公式解决。请各位指教。解答:1用公式:=SUM(IF(($A$1:$A$6>2)*($A$1:$A$6<5),B1:C6))
2
用数组公式:
{=SUM(IF($A$2:$A$7>2,IF($A$2:$A$7<5,$B$2:$B$7,0),0))+SUM(IF($A$2:$A$7>2,IF($A$2:$A$7<5,$C$2:$C$7,0),0))}

A1单元格为出生日期,可用=DATEDIF(A1,NOW(),"y")计算其年龄

这个公式是什么意思?K7=if(AND(R7>3000, Q7>0.5), "", P7)

意思是:如果R7单元格中的数值大于3000,并且Q7单元格中的数值大于0.5,则在K7单元格中显示空白,否则显示出P7单元格中的数据。

统计数据问题一例

各位朋友,如果我想统计50个数据中大于某个值的数据个数,(这个值是在使用时才输入某个单元格的),请问用什么函数,如何实现,谢谢。 如数据单元格为A1E10,值的单元格为A11答:1、使用下面的数组公式: {=SUM(IF($A$1:$E$10>$A$11,1))}
2
、输入以下函数: =COUNTIF(A1E10,">"&A11)

关于条件求和问题!

ABC,三列数据,如果A列符合要求,求B1*C1+B2*C2+......?答:使用数组公式: {=SUM((R2C1:R13C1="ab")*(R2C2:R13C2)*(R2C3:R13C3))}

请教关于条件乖积的求和问题

A列为部门名称,B列为姓名,C列为日工资额(20.00)D列为月出勤天数,我想在另一汇总表中汇总出各部门员工月工资总额(即:相应部门对应的C*D之和)。请问如何解决?解答:1=SUM((A4:A10="甲部门")*(C4:C13)*(D4:D13))假设你的a列存放部门名称,你的b列存放员工姓名,C列存放日工资,D列存放天数。计算“甲部门”的工资总额。注意,这是数组公式,输入完毕后按ctrl+shift+回车问:我试着把区域引用改为整列,出现错误,请指点!=SUM((date!A:A="甲部门")*(date!E:E)*(date!F:F))答:经试验,不能用整列方式,你可以适当的调整一个比较大的区域如a2:a100 a1为标题行

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/604457/viewspace-997240/,如需转载,请注明出处,否则将追究法律责任。

上一篇: EXCEL问题集合4
下一篇: EXCEL问题集合6
请登录后发表评论 登录
全部评论

注册时间:2009-02-04

  • 博文量
    62
  • 访问量
    336039