EXCEL问题大全（2）...

在EXCEL里取数值的一部分用什么函数

打比方说有一个数字123456，我只想取它的后三位，也就是说只需要456，应该用什么函数。
解答：假设A1的值为123456, 用=RIGHT(a1,3)可以取其后三位./你如果想取其中的几位，不妨试试mid函数。

用什么函数可以插入字符

00020304T0239 0400020304T0239 /00020404T0211 0400020404T0211 /00020604T0199 0400020604T0199 /00020704T0216 0400020704T0216 /00021304T0241 0400021304T0241 /00021404T0222 0400021404T0222 /00021504T0222 0400021504T0222 /00021704T0139 0400021704T0139 /就像上表一样，怎样用第一列的数据形成第二列的数据，即在特定位置加上几个相同字符串？
解答：b1="04"& a1
问：哪如果倒过来呢？
答：用公式A1=MID(B1,3,13)或A1=SUBSTITUTE(B1,"04","",1)

将文件保存为以某一单元格中的值为文件名的宏怎么写

解答：假设你要以Sheet1的A1单元格中的值为文件名保存，则应用命令：
ActiveWorkbook.SaveCopyAs Str(Range("Sheet1!A1")) + ".xls"
A1是文本串的话，使用这条命令： ActiveWorkbook.SaveAs Range("A1") & ".xls" 。当然，如果你的A1是路径，F1是文件名的话，可以这样写： ActiveWorkbook.SaveAs Range("A1") & Range("F1") & ".xls"

求余数的函数

比如：A1=28，A2=（A1÷6）的余数=4，请问这个公式怎么写？解答：=MOD(28,6)

评委评分

解答：用两个函数可解决：rank(排高低)average(求平均)。也可以用：{=(SUM($B$1:$B$10)-SUM(LARGE($B$1:$B$10,ROW($A$1:$A$2)))-SUM(SMALL($B$1:$B$10,ROW($A$1:$A$2))))/(COUNT($B$1:$B$10)-4)}(可以解决有多个最高和最低分的问题)。

数据校对

已知:A2=SUM(A3:A20),B2=SUN(B3:B20),C2=SUM(C3:C20),D2=SUM(d3:d20),E2=SUM(E3:E20)、又知A2=B2+C2=D2+E2 。需要解决的问题:当B2+C2或者D2+E2不等于A2时,系统自动提示错误,请各位高手给予指点,是否一定要通过宏才可以解决,有没有更简单的办法
解答：=IF(AND(B2+C2=SUM(A3:A20),D2+E2=SUM(A3:A20)),SUM(A3:A20)," : (")

如何在一个单元格中自动输入在另外一个单元格中输入的文字

解答：a2中输入a1的文字,则a2=indirect("a1")

在表格中查找我需要的东西并把该行所有的数据反映到另外的表格中去

解答：比如找表A中B列数值等于5的数。在表B中的A1单元输入：=VLOOKUP(5,A!B1:B4,1)

在EXCEL表里建立唯一索引在该列输入重复的数值后自动提示输入错误

解答：1、选定你要限制输入的列（假设是A2:A20），选菜单的“数据”－“有效性”；
2、在许可条件中选定“自定义”，在公式一拦中，输入你要求的限制， 例如：“=COUNTIF($A$2:$A$20,A2)=1 ”。
3、你还可以在“输入信息”和“出错信息”输入一些提示信息。
不过，你一定要注意！这个功能只能在你从键盘上键入数据时有效！拷贝和粘贴数据的操作是无效的。

发票小写金额填充

我输入123456.52它自动给拆成￥1 2 3 4 5 6 5 2 的形式并且随我输入的长度改变而改变？
解答：我所知函数不多，我是这样做的，如有更方便的方法，请指点
例如：
在A1输入小写金额，则：
千万：B1=IF(A1>=10000000,MID(RIGHTB(A1*100,10),1,1),IF(A1>=1000000,"￥",0))
百万：C1=IF(A1>=1000000,MID(RIGHTB(A1*100,9),1,1),IF(A1>=100000,"￥",0))
十万：D1=IF(A1>=100000,MID(RIGHTB(A1*100,8),1,1),IF(A1>=10000,"￥",0))
万：E1=IF(A1>=10000,MID(RIGHTB(A1*100,7),1,1),IF(A1>=1000,"￥",0))
千：F1=IF(A1>=1000,MID(RIGHTB(A1*100,6),1,1),IF(A1>=100,"￥",0))
百：G1=IF(A1>=100,MID(RIGHTB(A1*100,5),1,1),IF(A1>=10,"￥",0))
十：H1=IF(A1>=10,MID(RIGHTB(A1*100,4),1,1),IF(A1>=1,"￥",0))
元：I1=IF(A1>=1,MID(RIGHTB(A1*100,3),1,1),IF(A1>=0.1,"￥",0))
角：J1=IF(A1>=0.1,MID(RIGHTB(A1*100,2),1,1),IF(A1>=0.01,"￥",0))
分：K1=IF(A1>=0.01,RIGHTB(A1*100,1),0)
(编者注：公式中最后一个0应改为"")

排列问题

已知1，2，3，4共4个数字符号构成的4位数的全排列有256个，如：1234，2341，3245等等，现在我需要将这256个数字全部列出，如果用手写笔算的话既麻烦又容易出错，不知可否用Excel来解决这个问题
解答：在单元格输入公式“=1111+1000*INT((ROW()-1)/64)+100*MOD(INT((ROW()-1)/16),4)+10*MOD(INT((ROW()-1)/4),4)+MOD(ROW()-1,4)”拖放到A256。

在条件语句中如何实现符合某个时期的条件的记录进行统计

比如有1-12月份的记录单,需要实现对每个月里些数据的统计汇总/(格式如何)
解答：以下公式,A列为日期列,B列为数据,要求计算1月份的累计:
=SUM(IF(MONTH(A:A)=1,B:B,0))
此为数组公式,在输完公式后,不要ENTER,而要CTRL+SHIFT+ENTER.

行改列(不是转置问题)

1. 原内容为行（每行5列内容共几千行）：
列1 列2 列3 列4 列5
行1 1 2 3 4 5 /行2 1 2 3 4 5 /行3 1 2 3 4 5 /以下类同… /…
2. 改为行排列（每12行5列内容共12组，排为一行）
列1 列2 列3 列4 列5 列6 列7 列8 列9 列10 ……(60列)
原1-12行 1 2 3 4 5 1 2 3 4 5 ……(60列) /原13-24行 1 2 3 4 5 1 2 3 4 5 ……(60列) /原25-36行 1 2 3 4 5 1 2 3 4 5 ……(60列) /以下类推 …
本人不会编写宏，让您见笑曾费劲手工移动录制了一个取2400行（每行5列内容）改列的宏。因原有工作表行不断增加，经常需要行改列作其它用途，录制的宏不能满足需要。烦请大家帮助写一自动循环取12行5列内容排列为一行的宏。
规律：
1. 1-12行1-5列内容排在第1行（第一行1-5列、第二行1-5列、类推，完成为60列）
2. 13-24行1-5列内容排在第2行（第13行1-5列、第14行1-5列、类推）
3. 25-36行1-5列内容排在第3行（第25行1-5列、第26行1-5列、类推）
4. 以下类推 …
解答：Sub 转换()
Dim numcol As Integer
Dim numrow As Long
Dim i As Long
Dim x As Integer
Dim numperrow As Integer
numperrow = InputBox("请输入每行要填的数据行的数目:")
Range("数据").Select
numrow = Selection.Rows.Count '数据区的行数
numcol = Selection.Columns.Count '数据区的列数
x = numperrow * numcol
Range("a1").Select
For i = 1 To numrow '以数据的每一行为单位进行剪切
Range("数据").Rows(i).Cut
ActiveSheet.Paste
Selection.Offset(, numcol).Select
If (i Mod numperrow) Then '判断是否要换行
Else: Selection.Offset(1, -x).Select
End If
Next i
End Sub
'本程序需要把要变换的数据命名为"数据"(数据区的列数可以任意,不一定是5)方法:选中该区域,在名称框内输入"数据"即可,然后按程序要求输入每行要填的数据行的数目(如本例中的12)

如何给自动筛选中的自定义指定快捷键

解答：Sub 自动筛选()
On Error GoTo xx
Selection.AutoFilter = True
xx:
End Sub

汇总问题

本人有以下一个问题需要解决，请诸位大虾指点迷津。
　　如下表：
　　　A　　　B　　　　　C
　1　50　　采购　　（采购汇总） /　2　60　　工程　　（工程汇总） /　3　80　　工资　　（工资汇总） /　4　100　　税　　　（税汇总） /　5　70　　采购　　 /　6　50　　工资　　 /　7　60　　工程 /. . . /请问：C1、C2、C3、C4中的公式如何设置？
解答：方法1：如下表：
　　　A　　　B　　　　　C
　1　50　　采购　　=countif(a1:a7,"采购") /　2　60　　工程　　=countif(a1:a7,"工程") /　3　80　　工资　　=countif(a1:a7,"工资") /　4　100　　税　 　=countif(a1:a7,"税") /　5　70　　采购　　 /　6　50　　工资　　 /　7　60　　工程 /　. . .
方法2：用如下方法才对：　
　　A　　　B　　　　　C
　1　50　　采购　　=SUMIF(B1:B7,B1,A1:A7)
　2　60　　工程　　=SUMIF(B1:B7,B2,A1:A7) /　3　80　　工资　　=SUMIF(B1:B7,B3,A1:A7) /　4　100　　税　　 =SUMIF(B1:B7,B4,A1:A7) /　5　70　　采购　　 /　6　50　　工资　　 /　7　60　　工程
　方法3：由于Ｂ栏属于数据表的一部分，可能会随时改变，故此最好把采购等项目名称抄到另外一个地方（Ｃ栏）。
　　A　　　B　　　　　C　　　　D
　1　50　　采购　　采购　　=SUMIF(B$1:B$7,C1,A$1:A$7) /　2　60　　工程　　工程　　=SUMIF(B$1:B$7,C2,A$1:A$7) /　3　80　　工资　　工资　　=SUMIF(B$1:B$7,C3,A$1:A$7) /　4　100　　税　　 税　　　=SUMIF(B$1:B$7,C4,A$1:A$7) /　5　70　　采购　　 /　6　50　　工资　　 /　7　60　　工程 /还有，记着要适当地使用绝对参照地址 (Absolute referencing)。
方法4：
A　　　B　　　　　C　　　　D
　1　50　　采购　　采购　　=SUMIF(B:B,C1,A:A) /　2　60　　工程　　工程　　=SUMIF(B:B,C2,A:A) /　3　80　　工资　　工资　　=SUMIF(B:B,C3,A:A) /　4　100　　税　　 税　　　=SUMIF(B:B,C4,A1:A) /　5　70　　采购　　 /　6　50　　工资　　 /　7　60　　工

统计包含某个字符的个数

我想编的公式是： a/[84 - （b×4）] 。其中a是一个数值，小于或等于84；b是包含字符C的单元格的个数；C是一个符号。这个公式的关键是要统计出包含字符C的单元格的个数。
解答：方法1：=a/(84-countif(b,"=c")*4)
问题：我试了一下，不能运行，我想是因为没有指定出现“c”的单元格的范围。比如说“c”在D2—D30中随机出现，在上述公式中要先统计出出现“c”的单元格的个数。这个公式如何做？
解答： =a/(84-COUNTIF(D3:D30,"c")*4)

如何用if函数判断,如果a1单元格大于0,b1单元格为0是错误,为非0是正确

解答：1：if(and(a<=0,b=0),"错误","正确")
2：=if(a1>0,if(b1=0,"错误","正确"),"条件一不满足")

我改进以下一个验证先进先出法公式

=IF(B3<C1,IF(SUM(B3:B4)<C1,IF(SUM(B3:B5)<C1,IF(SUM(B3:B6)<B1,IF(SUM(B3:B7)<C1,"out of range",(B3*C3+B4*C4+B5*C5+B6*C6+(C1-SUM(B3:B6))*C7)/C1),(B3*C3+B4*C4+B5*C5+(C1-SUM(B3:B5))*C6)/C1),(B3*C3+B4*C4+(C1-SUM(B3:B4))*C5)/C1),(B3*C3+(C1-B3)*C4)/C1),C4)
B3至B7为输入数量,C3至C7为输入单价,C1为输入结余数量,E1为输出平均单价,即上面公式
解答：公式太复杂，而且受限制。
把A列利用起来，a3=SUM(B3:$B$7)，拖至7行（若为X行，则=SUM(B3:$B$X，下拖），A列数据为先进先出的累计数量（待Match的结存数量），=MATCH(C1,A3:A7,-1)找到结存批次。然后计算结存平均单价及发出平均单价。

有无简结一点的公式求如:a1*b1+a2*b2+b3*b3...的和

解答：在B4中输入公式"=SUM(A1:A3*B1:B3)",按CTRL+SHIFT+ENTER结束.
或：=SUMPRODUCT(A1:A10,B1:B10)

工作中须将表格中大量的日期同时转换为中英文的星期几

解答：转成英文：
=TEXT(WEEKDAY(A1),"dddd")
转成中文：
=TEXT(WEEKDAY(A1),"aaaa")

我想查询表格中的某一列编号中有221这一编号时，引用其同行某列的数值

我现用 VLOOKUP（221，A1：E33，5，0）这一公式引用其同行第5列的数值时可以使用，但当查询到没有 221 时，想使其返回值为“0”不知如何处理。
解答：=if(iserror(vlookup())=true,0,vlookup())

如何给自定义函数写上帮助信信息和参数说明

解答：目前自定义函数是不能做到完全象内置函数一样的。但是可以给它添加一段说明。
打开带有自定义函数的工作簿-〉按快捷键 Alt+F8—〉在光标处输入函数名字—〉单击“选项”—〉输入说明文字。

如何在EXCEL中统计一个数在哪一行出现

解答：{=IF(ISERROR(SMALL(IF(R1C1:R16C1=R1C2,ROW(R1C1:R16C1),""),ROW()-1)),"",SMALL(IF(R1C1:R16C1=R1C2,ROW(R1C1:R16C1),""),ROW()-1))}

在SHEET51的A1做公式加总SHEET1:SHEET50的A1怎么做啊

解答：=SUM(Sheet1:Sheet50!A1)

工作表间的数据输入

我想在sheet1中输入数据，例如我先在A1中输入1，确定是输入第一个单位的数据，然后从A2开始是第一个单位的数据。输入完成后，在SHEET2中的第一个单位数据就显示出来了，我的问题是，我想用SHEET1表再输入第二个单位的数据，但是这时SHEET2中第一个单位的数据没有了，显示是第二个单位的数据，如何才能保存第一个单位的数据，又能用SHEET1再输入第二个、第三个单位的数据？
解答：=IF(户汇总!$B$3<>1,"",户汇总!B5)