分享

Excel的一些使用技巧(3)

 阿杰168 2012-04-12

如何将名称相同的字段合并汇总,且只显示同一个名称

如:公交公司 50 60,交通集团 60 40,公交公司 100 600,大学 60 10
将以上数据表进行合并汇总,即两个公交公司合计为一个单位,后面的数据进行相应求和汇总。
结果:,公交公司 150 660,交通集团 60 40,大学 60 10
解答:用SUMIF同样能够实现,按照上次的公式+公式+公式+……,例:=SUMIF($A$1:$A$4,公交公司,$B$1:$B$4)+SUMIF(Sheet2!$A$1:$A$4,公交公司,Sheet2!$B$1:$B$4)+SUMIF(Sheet3!$A$1:$A$4,公交公司,Sheet3!$B$1:$B$4)+SUMIF(Sheet4!$A$1:$A$4,公交公司,Sheet4!$B$1:$B$4)。
也可以这样:=IF(Sheet1!$A$1:$A$4=公交公司,IF(Sheet2!$A$1:$A$4=公交公司,IF(Sheet3!$A$1:$A$4=公交公司,IF(Sheet4!$A$1:$A$4=公交公司,SUM(Sheet1!B1:B4+Sheet2!B1:B4+Sheet3!B1:B4+Sheet4!B1:B4)))))
中的内容根据自己的条件需要改变。
或者:用菜单“数据》合并计算”功能。

按工号大小排列

表1
工号 成绩 月份/01 20 1月/06 30 1月/100 60 1月/102 80 1月
表2
工号 成绩 月份/01 80 2月/07 90 2月/100 89 2月/102 90 2月/130 100 2月
要求产生的表:
工号 成绩/1月 2月 /01 20 80/06 30/07 90/100 60 89/102 80 90/130 100请问如何实现按工号大小排列?
解答:记录的自动筛选我已经解决,请仔细体会例子中公式的含义。多条件数据的求和,用数组公式: =SUM((条件1所在区域=条件1)*(条件2所在区域=条件2)*(要求和的区域))。文本用“ ”

两日期间的天数

Q:在一个表中有两列日期型数字 ,请问如何在第三列中得到其差(两日期间的天数)
A:=IF(A1>B1,DATEDIF(B1,A1,d),DATEDIF(A1,B1,d))

序列问题

我用EXCEL做了一个模板,其中一些数据(文本数据)是经常重复的,但我每次只好重新输入,作了很多重复劳动,请告知如何做一个选择对话框,把以往输入的数据(文本数据)显示以供选择,更好是能按字母查询。谢谢!!
解答:在B列设置数据验证,首先定义“名称”为ABC在引用位置设置为A1:A1000
然后将B列数据有效性设置为: ”序列“ ”=ABC“
在A列输入的数据在B列就会列出来

条件乘积的求和

A列为部门名称,B列为姓名,C列为日工资额(如20.00),D列为月出勤天数,我想在另一汇总表中汇总出各部门员工月工资总额(即:相应部门对应的C*D之和)。请问如何解决?
解答1:直接写公式有点困难,我的想法是用vba编程实现。思路如下:
编写一个循环,遍历A列, 并进行检测,设置几个变量,分别代表几个部门和相应的工资总额,每当部门变量和单元格的值相等时,就把该值加到相应的工资总额变量中,直到遍历结束。
解答2:采用数组公式:{sum((Aarray=部门名称)*(Carray)*(Darray)*1}
其中array为对应的区域名称!
解答3:1、使用数据透视表的功能。
2、或者使用SUMPRODUCT函数。

如何判断

如果 c2为空,则如果b2含有“1”,或者“2”,或者“3”的话,d2=b2*4-5,请问各位高手,以上如何在excel内实现判断.
解答1:D2=IF(AND(C2=,OR(B2=1,B2=2,B2=3)),B2*4-5,)
解答2:(上面的解答和题意不符,含有字符1、2、或3。应该写成d2=IF(C2=,IF(ISERROR(FIND(1,B2,1)>0),IF(ISERROR(FIND(2,B2,1)>0),IF(ISERROR(FIND(3,B2,1)),,B2*4-5),B2*4-5),B2*4-5),)

用字母在单元格里面填入平方米

解答1:在编辑状态下,选中“2”,按鼠标右键,选择“设置单元格格式”,选“上标”
解答2:按[ALT]+数字键178[ENTER]

Excel中打印条形码

解答:在Office中都可以通过在控件工具箱单击其它控件,选取Microsoft BarCode Control 9.0 ,然后进行赋值操作即可。

求工龄

=DATEDIF(B2,TODAY(),y)
=DATEDIF(B2,TODAY(),ym)
=DATEDIF(B2,TODAY(),md)
=DATEDIF(B2,TODAY(),y)&年&DATEDIF(B2,TODAY(),ym)&月&DATEDIF(B2,TODAY(),md)&日

26个表格汇总求和成一个汇总表

解答:假设汇总的工作表叫Sheet1,而存放数据的工作表叫Sheet2, Sheet3, ..., Sheet27,你可以这样设置公式,把各工作表A1的数值加起来:
  =SUM(Sheet2:Sheet27!A1)
要注意的是,Sheet1不能在Sheet2和Sheet27中间,Excel不会管工作表的名字,只会把Sheet2和Sheet27中间的*所有*工作表中相关的储存格(哪怕中间有一个工作表叫Sheet99)加起来。
1、在汇总表单元格内选择“Σ”
2、选择需要叠加的第一个工作表
3、按住shift同时用鼠标选择需要叠加的最后一个工作表
4、选择需要叠加的单元格
5、Enter

重复数据得到唯一的排位序列

想得到数据的出现总数吗({1,2,2,3,4,4,5} 数据的出现总数为5)?
解答:不需要插列,不需要很多的函数就行了. =RANK(B3,B$3:B$12)+COUNTIF(B$3:B3,B3)-1

从身份证号码中提取性别

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)
答:道理都是一样的,不过你的公式比那个公式优质。

如何从文本文件中取数

Q: 1.如何从文本文件中取2,4,6,8......行的数据到EXCEL中?
2.在EXCEL中如何实现产生一个ACCESS的表,并把指定的数据存入ACCESS的表中.
A: 1.(暂时不知道你问的目的是什么,如果仅仅要结果,就往下看)如果要导入一文本文件(过程略),导入的结果在SHEET1的A1:100(如果是100行的话)中,此时不管它奇还是偶行;然后在SHEET2的A1中输入公式=INDEX(Sheet1!$A$1:$A$81,2*ROW(),COLUMN()),拖公式,就可以截取到文本文件的2,4,6,8,10......行了。
2.关于EXCEL转为ACCESS,MS专门为用户设计了一个这样的ADD-IN,你可到
http://office.microsoft.com/downloads/2002/acclnk.aspx 当一个

怎样读取数字

Q: 怎样在一数字符串分解为单独的数字,比如将31234的值分解为3\1\2\3\4,读出后将相应的结果填充到固定的单元格
A:1用RIGHT() 或LEFT()
2.假设A1单元格为:31234,结果分别放入B1—B5,
B1=MID(a1,1,1);B2=MID(a1,2,1);B3=MID(a1,3,1);B4=MID(a1,4,1);B5=MID(a1,5,1)
3.用数据->分列->固定宽度...试试.
4.RIGHT(RIGHT(B2,1),1)
假设A1单元格为:31234,结果分别放入B1—B5,
B1=LEFT(LEFT(a1,1),1);B2=RIGHT(LEFT(a1,2),1);B3=RIGHT(LEFT(a1,3),1);B4=RIGHT(LEFT(a1,4),1);B5=RIGHT(LEFT(a1,5),1)

怎样才能快速算出

有这样一个问题:年终结帐:要将12个月的账分成二笔来结,(假定数据如下)假定一笔为300或最接近300,剩余的为另一笔。怎样才能快速算出? 1月 56.6 、2月 12.1 、3月 54.3 、4月 87.8 、5月 43.2 、6月 61.4 、7月 32.7 、8月 27.5 、9月 38.2 、10月 11.1 、11月 36.9 、12月 29.4 、总数 491.2
A: 我给出将12个月的账分成若干份(即是未知的)结,条件还一样。
这个弄明白了,你想要的自然就会做出了。
E F G
1 1月 56.6 =IF(F1>=300,F1,$F$1)
2 2月 12.1 =IF(ISNUMBER(G1),$F$ & ROW(G1),IF(SUM(INDIRECT(G1 & :F & ROW(G2)))>300,SUM(INDIRECT(G1 & :F & ROW(G1))),G1))
3 3月 54.3 =IF(ISNUMBER(G2),$F$ & ROW(G2),IF(SUM(INDIRECT(G2 & :F & ROW(G3)))>300,SUM(INDIRECT(G2 & :F & ROW(G2))),G2))
4 4月 87.8 =IF(ISNUMBER(G3),$F$ & ROW(G3),IF(SUM(INDIRECT(G3 & :F & ROW(G4)))>300,SUM(INDIRECT(G3 & :F & ROW(G3))),G3))
5 5月 43.2 =IF(ISNUMBER(G4),$F$ & ROW(G4),IF(SUM(INDIRECT(G4 & :F & ROW(G5)))>300,SUM(INDIRECT(G4 & :F & ROW(G4))),G4))
6 6月 61.4 =IF(ISNUMBER(G5),$F$ & ROW(G5),IF(SUM(INDIRECT(G5 & :F & ROW(G6)))>300,SUM(INDIRECT(G5 & :F & ROW(G5))),G5))
7 7月 32.7 =IF(ISNUMBER(G6),$F$ & ROW(G6),IF(SUM(INDIRECT(G6 & :F & ROW(G7)))>300,SUM(INDIRECT(G6 & :F & ROW(G6))),G6))
8 8月 27.5 =IF(ISNUMBER(G7),$F$ & ROW(G7),IF(SUM(INDIRECT(G7 & :F & ROW(G8)))>300,SUM(INDIRECT(G7 & :F & ROW(G7))),G7))
9 9月 38.2 =IF(ISNUMBER(G8),$F$ & ROW(G8),IF(SUM(INDIRECT(G8 & :F & ROW(G9)))>300,SUM(INDIRECT(G8 & :F & ROW(G8))),G8))
10 10月 200 =IF(ISNUMBER(G9),$F$ & ROW(G9),IF(SUM(INDIRECT(G9 & :F & ROW(G10)))>300,SUM(INDIRECT(G9 & :F & ROW(G9))),G9))
11 11月 36.9 =IF(ISNUMBER(G10),$F$ & ROW(G10),IF(SUM(INDIRECT(G10 & :F & ROW(G11)))>300,SUM(INDIRECT(G10 & :F & ROW(G10))),G10))
12 12月 29.4 =IF(ISNUMBER(G11),$F$ & ROW(G11),IF(SUM(INDIRECT(G11 & :F & ROW(G12)))>300,SUM(INDIRECT(G11 & :F & ROW(G11))),G11))
13 =IF(ISNUMBER(G12),,SUM(INDIRECT(G12 & :F & ROW(G13))))
14 总数 491.2
最后结果为:
E F G
1 1月 56.6 $F$1/2 2月 12.1 $F$1 /3 3月 54.3 $F$1/4 4月 87.8 $F$1/5 5月 43.2 $F$1 /6 6月 61.4 254 /7 7月 32.7 $F$6 /8 8月 27.5 $F$6 /9 9月 38.2 $F$6 /10 10月 200 159.8 /11 11月 36.9 $F$10 /12 12月 29.4 $F$10 /13 266.3 /14 总数 491.2 /说明:G6 为 F1:F5 之和、G10 为 F6:F9 之和、G13 为 F10:F12 之和。

如何实现对日期格式的要求条件

Q:在条件语句中如何实现符合某个时期的条件的记录进行统计,比如有1-12月份的记录单,需要实现对每个月里些数据的统计汇总/(格式如何?)
A: dongmu
以下公式,A列为日期列,B列为数据,要求计算1月份的累计:
=SUM(IF(MONTH(A:A)=1,B:B,0))
此为数组公式,在输完公式后,不要ENTER,而要CTRL+SHIFT+ENTER.(好象不足呀!程香宙)

截取单元格里某个字符后的字符

A: tof :使用RIGET()或LEFT()函数就可以了,详细可以参考EXCEL的帮助
liberty:比如这样:
a1内容为dfgsd2163.com,g2er.g
我需要提取出,号以后的字符,g2er.g
午餐 :先使用SERACH或FIND函数找出“,”的位置,再用MID来取数,如A1=123,456,我想取出“,”以右的数,可以=MID(SEARCH(,),8)。可能我用的函数不行,不过思路一定行。
  你多看看帮助文件,我在网吧,此机无EXCEL无法试,不过我以前用过,用MID加SEARCH一定行的通,多试试吧。
  实在不行再给我邮箱发信了,愿意帮忙!祝你好运。
  我认为能用函数解决的最好不用VBA,你说呢?
markxg :假设A1中有dfgsd2163.com,g2er.g
=RIGHT(A1,LEN(A1)-SEARCH(,,A1,1))
missle:我今天也试着用公式解决了你的问题,思路与MARK的一样,只是他用的是SEARCH,我用的是FIND。但不是很清楚你的问题,是否要把“,”号一起给截取下来,如果是的话:公式应该是=RIGHT(A1,LEN(A1)-SEARCH(,,A1,1)+1),否则“,”号是无法截取到的。
把问题给纵深一下,如果单元格内有多个“,”的话,如A1中是:aa,bb,cc,dd 我想截取 cc,dd 该如何作呢?或者想知道这个单元格中含有多少个“,”又应该如何呢?(VBA的解决方法除外)
午餐 :Missle,你看仔细了,在Search函数中是有参数的,它允许你指定在第几个出现显出位置,如“,”你可以通过指定search参数选定第几次出现时的位置,对于截取我更认为MID比LEFT和RIGHT好

if函数判断

请问:如何用if函数判断,如果a1单元格大于0,b1单元格为0是错误,为非0是正确?
A: dongmu
if(and(a<=0,b=0),错误,正确)
葡萄 :=if(a1>0,if(b1=0,错误,正确),条件一不满足)                              

分别显示总分最高的同学的班级、姓名、总分

Q:问题1是这样的:
1、在A1:A30单元格区域中输入500至600之间的数值;
2、在B1单元格中输入500;
3、在C1单元格中插入公式: COUNTIF(A1:A30,>=INDEX(B1,1)*0.9)
回车后C1单元格中显示的结果是0,为何不能把A1:A30中输入的数大于或等于500*0.9的单元格个数统计出来?错在哪里,该如何更正?
问题2有下面一个表格:
班级 姓名 政治 语文 数学 总分
1 小东 90 90 90 270 /2 明明 95 92 90 277 /3 小英 96 89 91 276 /要在K1:K3的单元格中分别显示总分最高的同学的班级、姓名、总分 .该如何设置公式?
答案1:建议你好好看看COUNTIF的帮助,真是搞不明白,为什么在B1里输入500而不是450呢? COUNTIF不支持一个以上的条件,你应该用DCOUNT。
答案2:
K1=INDEX(A2:F4,MATCH(K3,F2:F4,0),1)
K2=INDEX(A2:F4,MATCH(K3,F2:F4,0),2)
K3=MAX(F2:F4)
解答3:
问题一:公式改为:=COUNTIF(A1:A30,>= & INDEX(B1,1)*0.9)
问题二:在K1:K3中分别输入如下公式(假设A1为班级):
=LOOKUP($K$3,$F$2:$F$4,A2:A4)
=LOOKUP($K$3,$F$2:$F$4,B2:B4)
=LARGE(F2:F4,1)
解答4
第一题:=COUNTIF(A1:A30,>=&B1*0.9)
第二题:K1==INDIRECT(A&MATCH(MAX(F1:F4),F1:F4))
K2=INDIRECT(B&MATCH(MAX(F1:F4),F1:F4))
K3=MAX(F2:F4)

定有如上两列数据A和B 现在想要统计满足条件B=8的 并且在C列自动生成数据

Q: A B C
427 8 427 /612 8 612 /924 8, 924 /22 16 409 /94 16 /620 16 /955 16 /409 8
请问 :假定有如上两列数据A和B ,现在想要统计满足条件B=8的 ,并且在C列自动生成数据,我不懂公式怎么写?我知道筛选能够做到 ,但是由于数据量比较大 ,想做一个模板,免去一些重复劳动 .
A: dongmu
=IF(ROW($A1)>COUNTIF($B$1:$B$8,8),,INDEX($A$1:$A$8,MATCH(SMALL(IF($B$1:$B$8=8,0,1)+ROW($B$1:$B$8)/100000,ROW
($A1)),IF($B$1:$B$8=8,0,1)+ROW($B$1:$B$8)/100000,0)))
老夏 L:
B栏有100个选取条件,公式要修改100次 ,有甚么灵活性?,给字段名称,插入头两列.B1=SUBTOTAL(9,B4:B65536) .解决掉所有问题 ,控件找自动筛选.

排名问题

在A1:F6区域有下面一个表格:
班级 姓名 政治 语文 数学 总分
1 小东 90 90 90 270 /2 明明 95 92 90 277 /3 小英 96 89 91 276 /4 小刘 95 90 92 277 /5 小红 95 91 92 278 /要在K1:K3的单元格中分别显示总分最高的同学的班级、姓名、总分/在L1:L3的单元格中分别显示总分第二的同学的班级、姓名、总分 /在M1:M3的单元格中分别显示总分第三的同学的班级、姓名、总分 /注意期中277分的有两人,不要出现第二名与第三名都是明明的结果.
A: dongmu
定义A2:A6区域为班级 ;定义B2:B6区域为姓名;定义F2:F6区域为总分
K1={INDEX(班级,MATCH(LARGE(总分+1-ROW(总分)/100,ROW($A1)),总分+1-ROW(总分)/100,0))}
L1={INDEX(姓名,MATCH(LARGE(总分+1-ROW(总分)/100,ROW($A1)),总分+1-ROW(总分)/100,0))}
M1={INDEX(总分,MATCH(LARGE(总分+1-ROW(总分)/100,ROW($A1)),总分+1-ROW(总分)/100,0))}注:只要向下复制,便可得到1~6名的情况了。该公式可以无限排列。

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多