EXCEL技巧集锦-优快云博客

接：EXCEL问题集合1

[@more@]

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

解答：比如找表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(B3B3至B7为输入数量,C3至C7为输入单价,C1为输入结余数量,E1为输出平均单价,即上面公式解答：公式太复杂，而且受限制。把A列利用起来，a3=SUM(B3:$B$7)，拖至7行（若为X行，则=SUM(B3:$B$X，下拖），A列数据为先进先出的累计数量（待Match的结存数量），=MATCH(C1,A3:A7,-1)找到结存批次。然后计算结存平均单价及发出平均单价。

有无简结一点的公式求如:a1b1+a2b2+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)

请问如何用公式达到我想要的结果

A列 B列 C列
1 a
2 a
3 a
1 b
4 b 我想当B2=B3，而且A2=1时，C2的值为OK，请问该如何写这个公式？解答：=IF(AND(B2=B3,A2=1),"OK","")

一个页面在打印一次后是否能在上面的某一单元格数值上加1

解答：可使用 Workbook 的 Before_Print 事件. 按 [Alt + F11] 进入 VB Editor, 左侧会有一个 [ThisWorkbook],
(如果没有, 按 [Ctrl+R] 叫出) 在 [ThisWorkbook] 快按两下, 贴上程序 --
Private Sub Workbook_BeforePrint(Cancel As Boolean)
[A1] = [A1] + 1
End Sub

不用VBA，用EXCEL语句的IF函数，如何使用其条件关系

假如，现在A1，A2，A3;B1,B2,B3 只要B1 不为空B1＜＞“”，那么B2＝A2　B3＝A3 如何使用其IF语句来实现？？解答：B2=IF($B$1<>"",$A2)
B3=IF($B$1<>"",$A3)

求和问题

对于工作表中的数据库,随便举一例: 编号数量1 数量2
01 180 80
02 190 90
01 170 80
01 160 80 我如何计算(数量1-数量2)值的总和.并且必须满足编号相同时, 只能减一次数量2. 即要计算成: (180+170+160-80)+(190-90). 注: 编号相同时,数量2的值一定相同.解答：设表格在A1:C5中
=SUMIF(A2:A5）-INDEX(C1:C5,MATCH("01",A1:A5,0))-INDEX(C1:C5,MATCH("02",A1:A5,0))

排名问题

在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分的有两人,不要出现第二名与第三名都是"明明"的结果.解答：定义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名的情况了。该公式可以无限排列。另注：这是数组公式，不需要输入“{}”，在输完公式后，请按ctrl+shift+enter，应将后面的100改成100000，比较好。

统计满足条件的数据并生成新的数据列

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列自动生成数据，我不懂公式怎么写，我知道筛选能够做到，但是由于数据量比较大，想做一个模板，免去一些重复劳动。解答：=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)))争议：B栏有100个选取条件，公式要修改100次，有甚么灵活性?给字段名称，插入头两列，B1=SUBTOTAL(9,B4:B65536)。解决掉所有问题。控件找自动筛选要辩解：不需要修改公式，直接复制就行了。如果是100个，开始时就将范围修改好就行了。

工作表间的查找与复制

我的工作是在一个2000多个员工的纪录（包括各种资料，如身份证号，工作记录等）的excel工作表A中查找另一份200多个员工名单，把员工在A表中的各项纪录复制到另一个表上去，我听说用vba可以很容易的简化大量的复制工作，不知哪位高手可以指点指点？？解答：如下表sheet1的内容
a b c 姓名工号部门张三 100 mm /李四 101 mm2 /....... 表sheet2
a b c 姓名部门 /李四 /。。。要在sheet2表中将sheet1表的相同人员资料复制过来可在b表单元格b2中输入 ”=vlookup(a2,sheet1!a1:gg2000,3,false) 最好将sheet1的数据区先定义一下

比较运算符为何要用引号

(1) 何解在公式内，运用 "比较运算符号" 时，要把数据连同比较运算符号用 "双引号" 括住，而其它算术符号就不需要？例如：=Countif(A1:A30,">=10")是可以接受，但不加双引号时，便遭拒绝。
(2) 又请问：如上例，要比较的数值是要参照某单元格时，公式应怎样做？解答：这是CountIF()的规定。第二个参数为字符串，必须用双引号扩起来，除非条件是等于一个数值。比如：=COUNTIF(A1:A30,">=4")
=COUNTIF(A1:A30,"condition") 　　　=COUNTIF(A1:A30,220) 用引用作参数（假设B1值为4）：　　　=COUNTIF(A1:A30,">=" & B1)

ＥＸＣＥＬ中如何修改批注的字体和颜色

解答：视图－－批注然后在批注上单击右键选择设置批注格式

如何以进阶筛选命令筛选出“序号”为单数、双数、或者是任意间距值的records

序号姓名性别年龄
1 李1 男 30 /2 李2 女 25 /3 李3 女 22/4 李4 男 31 /：：：： /99 李99 男 28解答：在“序列”与“姓名”之间插入一个空列，在其第一格输入“条件”。在“条件”下面输入公式“=IF(MOD(A5,2)=1,"单数","双数")”，用“自动筛选”。

n2=sum(b2到m2) 、n3=sum(b3到m3) 、有没有办法让n那一列可以自动填充

就是说每增加一行，就会自动计算n那一列的值、多谢！解答：1：我有个笨方法，须用VBA，不知和你意否。
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 14 And Cells(Target.Row, 14).Formula = "" Then Cells(Target.Row, 14).Formula = "=SUM(RC[-12]:RC[-1])"
End Sub
2：假设需要被SUM的数据存放在B列(第一个数据存放在单元格B2),将以下公式COPY到你希望显示结果的单元格中:
=IF(LEN(B2)>0,SUM(B$2:INDEX(B:B, ROW(), 1)), "") 切记如果B列中有空单元格隔开了需要计算的数据, 必须以零填充该单元格.
3：这个问题不用想得很复杂，就是要把n2单元格输入公式后，拖曳向下复制公式。也许是我想得太简单了。

在工作表里有连续10行数据, 现在要每行间格2行, 要如何做

解答：1：如sheet1！$A$1:$D$10中有连续10行资料，在sheet2中把sheet1中的数据每行间隔2行
sheet2！A1中公式可用
=IF(ROW()=1,Sheet1!A1,IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$D$10,INT((ROW()-1)/2)+1,COLUMN()),"")) 然后填充公式（注意公式在SHEET2中的填充范围，超过范围会出错！）
2：小修改
=IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$Z$500,INT(ROW()/3)+1,COLUMN()),"")

当点到某单元格时单元格会出现一个下拉的列框，可以选取其中的数据

解答：[数据]-[有效性]-[序列]又问：好像选取数据只能在本页面，有无高招选取另外页面的数据源，甚至是另外工作薄的？解答：先在“插入-》名称”中定义好其它页面上需要引用的数据源的名称（AA）。然后 [数据]-[有效性]-[序列] ，在“数据源”栏输入“=AA”。或：若改其它活页薄要如何修改下列式子?( 假设活页薄名称为book1)
=Sheet1!$A$2:$A$14(book2)

查找与引用

有一个表格：承兑行天数实际天数深圳工行 98 101 广州交行 105 108 鞍山工行 100 100 如果“承兑行”一栏含有鞍山字样，则“实际天数”等于“天数”；如果“承兑行”一栏不含有鞍山字样，则“实际天数”等于“天数”加上3，请问：如何设置？解答：这个问题的重点在于如何判断是否含有“鞍山”字样。如果“鞍山”在字符串中的位置是固定的，则很容易用MID函数找到并进行判断；如果不是，则需要用FIND或Search函数来找到再判断。或：如果A列中是承兑行，B列是天数，C是.... 可以试用一下以下的公式：
=IF(ISERROR(FIND("鞍山",A2)),B2+3,B2)

如果D2>20那E2就显示$200、如果D2>30那E2就显示$300依此类推

解答：=INT(D2/10)*100 ，当然，你的单元格格式设置成$格式就可以了。否则用，="$"&INT(D2/10)*100 则该单元格成字符型。当然，你也可以用IF函数，但它有7层的限制。if (D2>30, "300",IF(D2>20,"200"))

在两个工作表之间查找相同的内容并复制到新表

有两张工作表，内容都是电话号码、用户名称，怎样对两张工作表进行比较？（把第一张工作表和第二张工作表中电话号码相同的项目复制到新工作表中或加上标记）解答：先备份工作簿。假设SHEET1、SHEET2 两张表的结构相同，A 列为用户名，数据由第二行开始。新建SHEET3 并在其A2 单元格中输入 =IF(ISNA(MATCH(Sheet2!$A2,Sheet1!$A:$A,0))," DELET THIS ROW ",Sheet2!A2) 并向下、向右填充。完成后SHEET3中多了SHEET2 和SHEET1 共有的用户名，以及由"DELET THIS ROW"填充的数据行。剩下的就简单了！

统计偶数单元格合计数值

解答：统计F4到F62的偶数单元格合计数值。公式一｛=SUM(IF(MOD(ROW(F4:F62),2)=0,F4:F62))｝公式二 (要有安装'分析工具箱')｛=SUM(IF(ISEVEN(ROW(F4:F62)),F4:F62))｝

按表1中A列的编号提取表1中的相应数据并自动录入到表2中的对应的相关列

有两个工作表，工作表1中A列为编号，其它列为相关数据，若在工作表2中的编号列单元格中输入编号时，如何才能按表1中A列的编号提取表1中的相应数据，自动录入到表2中的对应的相关列。解答：利用vlookup寒暑，该函数的用处就是把一个数据区域当成数据库，并利用条件检索相关纪录。有了这点认识就非常简便了！在表2的单元格中输入下面的公式：
=vlookup(编号，表1！有关数据区域，第n列，false) 解释： 1、编号——不需要输入，主要是编号的相对引用。例如：编号在b2,公式在c2,则编号为：b2
2、有关数据区域——必须是绝对引用，也就是数据区的行列要用$符号修饰。例如：从a1到h50是数据，则应该写为：$a$1:$h$50
3、第n列——也就是你准备返回第几列的值。例如：你输入编号后，要得到姓名，而姓名在数据区域的第5列，n就是5。
4、false——此处取值有两种，一是true,一是false。两者的区别是 true为相似匹配，false为精确匹配。

用EXCEL做统计

如：A1，B1单元格是时间类型，C1是数字类型，我要计算费用用（B1-A1）*C1得到的数据还是时间类型、怎么办？我希望把时间类型变为整数类型，如0：50（50分钟）*3（元/H）=2.5元解答：设A1为3：30，B1为4：30，C1为3，D1为下列输入的函数： =HOUR(B1)*60+MINUTE(B1)-(HOUR(A1)*60+MINUTE(A1)) 。D1结果等于60（分钟）

单元格是否有注释

我在用VBA写EXCEL的注释时，需要等程序判断该单元格是否有注释，如果有就读出来，如果没有就添加，但我一直没找到用什么办法来知道单元格是否有注释，请各位大虾指点！！！解答：

Sub 批注()

For i = 1 To 8
On Error Resume Next
a = Sheets(1).Cells(i, 1).Comment.Text
If Err.Number = 91 Then
Sheets(1).Cells(i, 2) = "左侧单元格无批注"
Sheets(1).Cells(i, 1).AddComment Text:="请输入批注内容"
Else
Sheets(1).Cells(i, 2) = "左侧单元格批注" & a
End If
Next i
End Sub

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

转载于:http://blog.itpub.net/604457/viewspace-997237/