ITPub博客

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

EXCEL问题集合

原创 IT综合 作者:jackdear 时间:2008-01-14 00:27:52 0 删除 编辑
接:EXCEL问题集合2[@more@]A列记录几百条,如何对这列计数(重复的数值不计)

我只能做到新建一列,B列,然后第一个单元格countif$A$1:$A$100,A1),然后拖动到全部新列。最后在新列下面用sumif(B1:B100,1) 谁有更好地方法?解答:1、试试这个:{=SUM(IF(COUNTIF(A1:A100,A1:A100)=1,1,0))}
2
操作:①A1作公式栏,A2作字段名栏,如原该两栏有数插入2行。在A1输入:=SUBTOTAL(3,A$2:A$5000) 统计记录数或:=SUBTOTAL(9,A$2:A$5000) 数据汇总②选:数据-->筛选-->高级筛选-->选择不重复的记录。③复原选:数据-->筛选-->高级筛选-->全部显示。
3
、试试这个: {=SUM(IF($A$1:$A$100="","",1/(COUNTIF($A$1:$A$100,$A$1:$A$100))))}
4
、请解释一下,因为我单独使用COUNTIF($A$1:$A$100,$A$1:$A$100)数组公式时,它仅仅计算第一个也就是A1的个数.
5
、我发觉你的这办法,只对唯一的数据进行了计数,而重复的数据全部未计入(是不是应该将重复的数据也计上一个?)打哈欠的“{=SUM(IF(COUNTIF(A1:A100,A1:A100)=1,1,0))}”也是这样。
TO
剑魔版主你公式中的“1/(COUNTIF($A$1:$A$100,$A$1:$A$100))”像是一个倒数,怎么理解?
6
用倒数是这个意思:如果只出现一次,数组中的相应项统计为1,其倒数为1Sum统计计1如果出现 N 次,其倒数为1/N,出现了N次,求和就是Nx1/N,最后Sum统计就只计1

如果有文本串"YY0115",我想取第三、四的值"01",应该用什么函数

解答:1=mid("YY0115",3,2)&""
2
、如果你的A1中的数值一定包含后四位阿拉伯数字的话,你可以用这个:
=LEFT(RIGHT(A1,4),2)=LEFT(RIGHT(A1,4),2)=LEFT(RIGHT(A1,4),2)
3
、我的实际工作中的数据还没有如此规律,该怎么办?如:A1="YY0105"A2="99065"A1"01"A2"99"。能否用一个函数去掉A1"YY",然后都是从阿拉伯数字的第一位开始取两位数?
4
、如果阿拉伯数字数量不定,但是以2个英文字母开头(或无英文字母),可以用以下公式:(设数据在A1)
=IF(ISNUMBER(VALUE(A1)),MID(A1,1,2),MID(A1,3,2))
5
、数组公式:
{=MID(A1,MIN(IF(EXACT(LOWER(MID(A1,ROW(INDIRECT("A1:A256")),ROW(INDIRECT("A2:A257")))),UPPER(MID(A1,ROW(INDIRECT("A1:A256")),ROW(INDIRECT("A2:A257"))))),ROW(INDIRECT("A1:A256")),"")),2)}

怎样将文字和数字分2列显示

中行41785015110010091252、青泥支行200303004500696、卡伦办事处801017651、站前支行0709000309221004055 、金州支行400301459508091解答:1用函数可以解决。 假如A1 为 中行41785015110010091252B1=LEFT(A1,(SEARCHB("?",A1,1)-1)/2)C1=MID(A1,LEN(B1)+1,50)、随后将B1C1的公式往下复制。
2
Sub 分列()
For Each jk In Sheet1.UsedRange.Columns(1).Cells
For i = 1 To Len(jk.Formula)
If Abs(Asc(Mid(jk.Formula, i, 1))) < 256 Then
Sheet1.Cells(jk.Row, (jk.Column + 1)).Formula = "'" & Right(jk.Formula, (Len(jk.Formula) - i + 1))
jk.Formula = Left(jk.Formula, (i - 1))
Exit For
End If
Next i
Next
End Sub
3
、是否将Asc(Mid(jk.Formula, i, 1)) < 256 改成 47 < Asc(Mid(jk.Formula, i, 1)) < 58 会更好,因为它只将数字抽出,如果数字前有英文字的话将抽往数字列。
4
、我看帮助中instr函数不错配合循环速度应快些

500个不连续的数之和的求法

我有一个表格,有一列数据是以6 个单元格为一个单位做一次小计,我在小计中加入了公式 但是我还想求一下这个小计的总合,我想加入一个公式,但是我有SUM到了50多个时就加不进去了,用加号连加也不行,不知这个问题怎么解决解答:用SubTotal()解决即可

如何使输入的英文单词第一个字母变成大写

解答:Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = Application.WorksheetFunction.Proper(Target.Text)
End Sub

在一个表中有两列日期型数字请问如何在第三列中得到其差(两日期间的天数)

解答:=DATEDIF(A1,B1,"d")问:我用了datedif发现一个问题 、即当A列时,结果正确,而当A>B列时出错了 、请问用什么办法解决 答:=IF(A1>B1,DATEDIF(B1,A1,"d"),DATEDIF(A1,B1,"d"))

重要的EXCEL文件坏了(文件带密码)有没有EXCEL修复工具

解答:Excel 2000 数据>>取得外部数据>>新增数据库查询>>Excel File*>>找到档案>>
[
选项]勾选所有选项>> 会找到所有未命名[区块]Sheet1$
>>
找到字段>>其它跟着查询精灵导引一步一步作 此中间层组件叫作Microsoft Query 可以用来拯救[毁损档案]

只要求简单的把数据从分表直接追加到总表

解答:你可以用Lookup,vlookup等函数。

A1中输入11:22:22 要求每间隔一秒刷新一次

解答: sub mytime
range("a1")=now()
Application.OnTime Now + TimeValue("00:00:01"), "mytime"
end sub

怎样用函数来显示某月的最后一天是几号

答:= DAY(DATE(年份,月份+1,1)-1)

如何用excel求解联立方程

x-x(7/y)^z=68
x-x(20/y)^z=61
x-x(30/y)^z=38
解答:这是一个指数函数的联列方程。步骤如下
1
、令X/Y=W 则有
X-(7W)^z=68
X-(20W)^Z=61
X-(30W)^Z=38
2
、消去X
(20^Z-7^Z)W^Z=7
(30^Z-20^Z)W^Z=23
3
、消去W
(30^Z-20^Z)/(20^Z-7^Z)=23/7
由此求得Z=3.542899
x=68.173955
y=781.81960

请问如何在函数中同时使用两个条件

:IF同时使用条件B1>0B1<10 解答:and(B1>0,B1<10)

TRIM把“中 心 是”中间的空格去掉

解答:用SUBSTITUDE()函数,多少空格都能去掉。如A1中有:中 心 是 则在B1中使用=SUBSTITUTE(A1," ","")就可以了。注意:公式中的第一个“ ”中间要有一个空格,而第二个“”中是无空格的。

EXCEL中的单元格定义成数组

我在编写程序时遇到这样一个问题, 每次用循环程序时向单元格写或读数据时总是面向一个固定区域,Range("A1:D10") 。请问有没有方法能做到像数组那样,将单元格的下标由常量变成变量!。如下面这种形式,那编程时会减少很多的工作量!Range("A[value1]:D[value2]") 解答:range(cells(行数,列数),cells(行数,列数))、行数 列数可用变量带入。或:Range("A" &trin(str(value1)) & ":" & "D" & trim(str(value2)))

将单元格行、列高与宽单位设置成毫米

解答:其实行高和列宽的换算总题是不太复杂的,只要用打印机打印一个单元格的框架,再用尺子量出单元格框架的长和宽,然后用这个值跟单元格的磅值进行换算即可. :单元格的宽度是8.38,高是14.25.打印出来后宽度是19.6mm,高是6mm,这样就得出磅和毫米之间的换算关系
.
:19.6mm/8.38=2.339mm/; :14.25/6mm=2.375/mm 好了,我想不用我多说你就会制出跟实际表格一模一样的表格来.

一些电话号码如果是8结尾,号码加1;如果不是,号码加0.

解答:1、如果A11008  在B1中输入 =if(right(a1,1)=8,a1+1,a1),可以实现如果结尾是81,不是8则不加。2、如果不是上述意思则为 =if(right(a1,1)=8,concatenate(a1,1),concatenate(a1,0))3、实际上应该是:=if(right(a1,1)=8,a1&"1",a1&"0")

当做日报表时,怎样让月累计数自动加上

就是要月累计自动加上今天的当日收入数,今天只输入当日收入,我想用用前一天的月累计数加上今天的当日收入数为今天的月累计数.情况是一月一个工作薄,每一个工作薄下30个工作表,用每一天的日期为报表名.我想把月累计的公式写为 =sheet17!c5 中的 17 day(now())-1 的值去取代他,该怎么办呢? 以下为报表格式,谢谢大家帮我想一想. 部门-------当日收入 -----------月累计 团队收入 12.12 123.00 写字间收入 147,258.00 147.147 房内吧收入 147,258,369.00 解答:1=SUM('Sheet1:Sheet30'!C5) 在你需要月汇总的单元格填入上述公式,其作用是将工作表1到工作表30的“C5单元格的值全部累加起来,而“C5单元格应填入当日的收入数。 “我想把月累计的公式写为 =sheet17!c5 中的 17 day(now())-1 的值去取代他,该怎么办呢? ---这可能需要VBA才能实现。2 如果工作重复的话,可以制作一个模板,公式事先定义好,每次用时新建一工作簿即可。 “把月累计的公式写为 =sheet17!c5 中的 17 day(now())-1 的值去取代他”,还没找到方法,调试好了再告诉你。3 =INDIRECT(ADDRESS(1,1,1,1,"sheet"&DAY(NOW())-1))

单元格A1=a2,a3,a4,a5)其中一个就返回AA,如果A1=a6,就返回BB,否则就返回CC

解答:=IF(OR(A2=A1,A3=A1,A4=A1,A5=A1),"AA",IF(A6=A1,"BB","CC"))

关于引用及计算的问题

Sheet1!A1Sheet2!A3 /Sheet1!A2Sheet3!A3 /Sheet1!A3Sheet4!A3/........ /能有什么方法可以快速得出Sheet1!A4及以下的数值?(比如说至Sheet1!A100/还有就是 /Sheet1!A1Sheet2!A3+Sheet2!B7 /Sheet1!A2Sheet3!A3+Sheet3!B7 /Sheet1!A3Sheet4!A3+Sheet4!B7/........ /又该如何实现?解答:1=INDIRECT("Sheet"&ROW()+1&"!A3")2、数量1 =INDIRECT((ROW()-1)&"!$B$3")数量2 =INDIRECT((ROW()-1)&"!$C$4")+INDIRECT((ROW()-1)&"!$D$5")

如何统计某个列有数字的个数

解答:=COUNT(A:A)

如何统计此次自动筛选 出来共有的记录条数

解答:用 counta 统计

怎么把等于A栏里的一个值的,再统计出B栏里不同数据的个数

Angel [A] Beer [B]
a
北京 /a 广州 /a 天津 /b 广州 /b 长沙 /a 北京/b 北京 /b 长沙我还想再加个条件呢?比如: A栏是a的有3个不同的城市,A栏是b的有3个不同的城市,))...解答:1{=sum((a1:a10="a")*(b1:b10="广州"))}2、你搞错我的意思了,我是想要不同城市的值,:A栏中等于a,就统计出B栏中有多少个不同的城市数量,其结果是3(三个不同的城市)3、就你这道例题来讲:设数据在A1:B8中: {=COUNT(LARGE(IF($A$1:$A$8="a",(CODE(LEFT($B$1:$B$8))&CODE(RIGHT($B$1:$B$8)))*1,""),ROW($A$1:$A$5)))-IF(LARGE(IF($A$1:$A$8="a",(CODE(LEFT($B$1:$B$8))&CODE(RIGHT($B$1:$B$8)))*1,""),ROW($A$1:$A$5))-LARGE(IF($A$2:$A$9="a",(CODE(LEFT ($B$2:$B$9))&CODE(RIGHT($B$2:$B$9)))*1,""),ROW($A$1:$A$5))=0,1,0)}公式也有局限性,就是城市最多两个字,三个四个也可以,但肯能会出错,就是会漏掉

如何用IF函数达到我想要的结果

工作表数据如下:
A
B C D E
1 5 9 H /2 6 0 I /3 7 F J /4 8 G K /
我想当A=123 时,E=A列对应的值,/否则CONCATENATE(A2,B2,C2,D2) /结果如下:
A
B C D E
1 5 9 H 1 /2 6 0 I 2 /3 7 F J 3 /4 8 G K 48GK /
请问这个公式怎么写?解答:=IF(OR(A1=1,A1=2,A1=3),A1,A1&B1&C1&D1)

关于实现“查找并替换”的宏

我相要编写一个自动替换的宏。 要替换的内容如下: 把特殊符号“▼”替换为 即把原特殊符号前后各加上一个换行符。解答:Sub Macro()
Cells.Replace What:="
", Replacement:=Chr(10) & "" & Chr(10), lookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

用了一下,有问题,把, SearchFormat:=False, ReplaceFormat:=False删除后就能用了,不过,原有的文本格式设置都没有了,有点儿得不偿失了,呵呵。

我在想,删除里的“ReplaceFormat”是用来指定格式的吧,可我看了“Replace”的帮助,没有这个参数设置的。我用的是Execl2000,不知道有没有联系。

有关输入数字的提示

①、可作为数字使用的字符 在 Microsoft Excel 中,数字只可以为下列字符:

0 1 2 3 4 5 6 7 8 9 + - ( ) , / $ % . E e

②、Excel 将忽略数字前面的正号(+),并将单个句点视作小数点。所有其它数字与非数字的组合均作文本处理。

③、输入分数 为避免将输入的分数视作日期,请在分数前键入 0(零),如键入 0 1/2

④、输入负数 请在负数前键入减号 (-),或将其置于括号( )中。

⑤、对齐数字 在默认状态下,所有数字在单元格中均右对齐。如果要改变其对齐方式,请单击“格式”菜单“单元格”命令,再单击“对齐”选项卡,并从中选择所需的选项。

⑥、数字的显示方式 单元格中的数字格式决定 Excel 在工作表中显示数字的方式。如果在“常规”格式的单元格中键入数字,Excel 将根据具体情况套用不同的数字格式。例如,如果键入 $14.73Excel 将套用货币格式。如果要改变数字格式,请选定包含数字的单元格,再单击“格式”菜单上的“单元格”命令,然后单击“数字”选项卡,再根据需要选定相应的分类和格式。

⑦、“常规”数字格式 如果单元格使用默认的“常规”数字格式,Excel 会将数字显示为整数(789)、小数(7.89),或者当数字长度超出单元格宽度时以科学记数法(7.89E+08)表示。采用“常规”格式的数字长度为 11 位,其中包括小数点和类似“E”和“+”这样的字符。如果要输入并显示多于 11 位的数字,可以使用内置的科学记数格式(指数格式)或自定义的数字格式。

⑧、15 位限制 无论显示的数字的位数如何,Excel 都只保留 15 位的数字精度。如果数字长度超出了 15 位,Excel 则会将多余的数字位转换为零 (0)

⑨、将数字作为文本输入 即使用“单元格”命令将包含数字的单元格设置为“文本”格式,Excel 仍将其保存为数字型数据。如果要使 Microsoft Excel 将类似于学号之类的数字解释为文本,需要先将空单元格设置为“文本”格式,再输入数字。如果单元格中已经输入了数字,需要对其应用“文本”格式,然后单击每一个单元格并按 F2 键,再按 ENTER 键重新确认数据。

⑩、区域设置 可作为数字使用的字符取决于“控制面板”中“区域设置”内的选项。这些选项也决定了数字的默认格式,例如:在美国系统中句号 (.) 作为小数点使用。

如何判断某个单元格包含某个字符

解答:设A1=LOVE,查找字母L是否在A1中, =IF(ISERROR(SEARCH("L",A1)),"NO","YES")

按条件设定数值

我想定义单元格A1,在下列条件下数值不同: 1.B1大于0小于20时等于6; 2.B1大于20小于36时等于4; 3.B1大于36小于56时等于2; 4.大于56以上等于1.请问:A1公式应该如何?解答:=IF(B1=<0,"OUT",IF(AND(B1>0,B1<=20),6,IF(AND(B1>20,B1<=36),4,IF(AND(B1>36,B1<=56),2,1))))又问:开头的"B1=<0,"OUT","是起什么作用的?又答:从数学角度来说,一个有理数的范围是从负无穷到正无穷的。在你给我的B1的数值范围内,只定义了从0到正无穷,对于当B1<0=0时没有定义。而我在做IF函数时,必须要考虑到当B1处在<=0的范围时,A1需要回返的结果。所以我自己把它定义为,当B1<=0时,返回文本OUT,你自己可以把OUT改成任意你想出现的数字。另外,在你开始的条件定义中,都是当B1大于多少,小于多少,这也是不严格的,因为你没有定义当B1=0=20=36A1要返回的值。因此,我在写函数时,也自己帮你加了上去。关于这一点,你也可以在函数中自己改成你需要的定义范围。总之,在做IF函数时,请一定考虑到数值可能产生的所有范围及其对应返回的值。再问:怪我没对您说明白,我的文件里,B1永远是大于0.再答:如果你确定B1里出现的数值永远是大于0的,那么此函数可以简写为:
=IF(AND(B1>0,B1<=20),6,IF(AND(B1>20,B1<=36),4,IF(AND(B1>36,B1<=56),2,1)))
返回的结果与原先是一样的。但是,容我在此提醒你,在这个函数里,它与原先函数的区别是当B1>56B1<=0时,返回的值是一样的。所以,当A1返回的值=1时,无法判断是因为B1>56,还是因为B1<=0(按你的定义即为B1输入的数值有误)。所以,本人还是建议要将所有的情况都考虑在内。

小数的进位问题

excel,我想将小数点后所有的有效数都进为1,请问用什么方法? 、如:3.254.65.3....等进位为456....... 、说明:以上数值均为公式的计算结果,是可变的.解答:C3 = 3.25 、则在D3中输入“=ROUNDUP(C3,0)

找出A列的数字在B列中所在的位置, 并在第三列显示单元格号

解答:假设数据在A1:B10,则C列公式为
=MATCH(B1,$A$1:$A$10,0)

G9G71,需要隔行求和

解答:在需要的单元格中(如G72单元格)输入 :=SUM(IF(MOD(ROW(G9:G71),2)=1,G9:G71,0)) Ctr+Shift+Enter即可。

在单元格返回工作表名称

解答:函数方法: =MID(CELL("filename"),FIND("]",CELL("filename"))+1,100)

多条件计算公式

countif(a1:a100,"a")只等求取一个值的合计个数两个以上条件的个数用下列公式: sum(if(a1:a100="a",if(b1:b100<50,1,0)。也可以用数组公式:{=SUM((A1:A100="a")*(B1:B100<50)}

如何统计求每天不重复的值

如图所示:我想统计算出每天不重复的管理员?日期行数中的值每月每天都是上下午2个单元格分别以数字代表;管理员行中的管理员每月每天就没有固定的取值了,但人员数是不变的。图中管理员需求的值为:A=9B=7C=3如图:

解答:
C2=A
C3=B
C4=C
D2:{=SUM(($B$2:$B$21=C2)*(MATCH($A$2:$A$21&$B$2:$B$21,$A$2:$A$21&$B$2:$B$21,0)=ROW($A$1:$A$20)))}
拖到D4

使用vlookup函数的问题

当时有两千多人的考试成绩要与花名册挂接,考试成绩放在sheet km1中,花名册放在sheet hmc中,他们共有字段为准考证号,我的想法是根据准考证号,用vlookup函数查找相应的成绩并放在相应的人员下。
sheet km
的准考证号放在第一列,考试成绩放在第二列,查找范围是$a$2:$b$2265sheet 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

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

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

注册时间:2009-02-04

  • 博文量
    62
  • 访问量
    336043