EXCEL使用技巧及问题解答（函数&宏语句）

目录

我在excel中想实现这么一个功能，如单元格D12有一个数据是4，现在我想引用A4的数据，但4是由D12提供的，即如何实现A4=A(D12)。也就是，在Excel中，A7单元，能否实现把后面的数字用算式来代替，如A(3+4)，或者是单元格的嵌套，A(D12），恳请高手解答。

解答：①=indirect("a"d12)

②我的想法：借一个单元格如B4用，键入="A"D12在需引用的单元格键入=INDIRECT(B4)

即可，第二个：C4是3，C5是4B4="A"C4+C5,同①。

以下宏将在a列传回所有工作表名称。(很实用)

SubMaco1()

Fori=1

Cells(i,1)=Sheets(i).Name

Next

Sub

相信在EXCEL这个软件中如何输入15位或18位身份证号码的问题已经困挠了许多人，因为在EXCEL中，输入超过11位数字时，会自动转为科学计数的方式，比如身份证号是：123456789012345，输入后就变成了：1.23457E+14,要解决的方法有非常非常多种哦，呵呵，现在为大家说几种比较简单快速的方法：

1、在A1单元输入号码时，在前面先输入'号，就是：'123456789012345,这样单元格会默认为该单元为文本方式，会完整显示出15个号码来，而不会显示那令人讨厌的科学计数方式来了。

2、如果已经输入了大量的号码，全部以科学计数显示的话，而又懒的按照上面的方法一个一个的重新输入的话，也有这个好方法哦，比如已在单元A1：A100输入了号码，按以下步骤做：》选择单元A1：A100》单击鼠标右键，设置单元格式》选择自定义，在'类型'中输入'0'即可，轻松搞定，呵呵！！

3、还有一种用函数来解决的方法：在A1：A100已经输入大量的号码时，在B1单元中输入：=trim(''a1),注意两个'之间是空格，这个公式的意思:先在A1单元15位号码前加个空格符号，然后用trim这个函数把空格符号去掉。输完后，B1单元也是显示出完整的15位数字了。

解答：①你可以综合一下呀!=countif(a1:a9,"=50")-countif(a1:a9,"60")

②{=SUM((a1:a960)*(a1:a950))}

③为什么{=SUM((a1:a960)*(a1:a950))}可以求出符合条件的个数，按理后面应再加一待求和区域如"B1:B9"等等，不知数组计算的内部机制是怎样的，不知谁能给个解释,谢谢！！

④这个数组公式是这样运算的：a1:a9区域的数值分别与60比较。假如a1:a9的数依次为15,25,35,45,55,65,75,85,95.那么(a1:a960)返回{true,true,true,true,true,false,false,false,false}，同理(a1:a950)返回｛false,false,false,false,true,true,true,true,true}。然后(a1:a960)*(a1:a950)则返回{false,false,false,false,true,false,false,false,false}。因为*表示and的意思。只有当and的对象都为真时结果才为真。。excel里用1表示true;0表示false。有时需要将true或false转换成数值，可以*1或+0

解答：先按Ctrl键+其它要一起打印的工作表就会成为一个群组,打印的页数可延续到下一个Sheet

要求在给定的应收日期、滞纳金率、当前日期（自动取）的基础上自动计算出应收滞纳金。

解答：=(DATEDIF(应收日期,NOW(),"d"))*滞纳金率（每天）*应收金额

AB

234230

12

1013

如何用函数计算出下面几个数据

1。A和B都有数据且AB的个数

2。A栏在相应的B栏有数据的情况下的数据总和，例如A1对应B1有数据，A3对应B3有数据，则求A1+A3的和。希望都能用一个函数求出所要的结果。谢谢

解答：=SUM(($A$1:$A$10"")*($B$1:$B$10"")*($A$1:$A$10$B$1:$B$10))

=SUM(($A$1:$A$10"")*($B$1:$B$10"")*($A$1:$A$10$B$1:$B$10)*($A$1:$A$10+$B$1:$B$10))

记得输入公式后要按ctrl+shift+enter

解答：请运行以下程序：(测试通过)

SubConvertToUpperCase()

DimRngAsRange

Worksheets("Sheet1").

=FalseThen

=UCase()

If

NextRng

Sub

c1=COUNTIF(b1:b130,b129)，（b129单元格内的字符是"F"），问题是随后在向表格中添加新行131、132、133..之后，c1单元格中公式统计的结果还是1-129行的，怎么才能让c1中实时显示添加新数据后的统计结果?

解答：c1=COUNTIF(b:b,b129)

现有一表格，每天记录50辆不同车号的车辆的"出车里程"和"回库里程"，需要打印一清单，自动统计每辆在库车辆的当前里程，请问该如何做(结果放在其它sheet中，为方便显示这里和数据放在一个sheet中了)？

解答：可以用数组公式:

假设"此处数据不断增加"所在工作表的名字为"SHEET1".在"此处需要更新"所指的单元格内输入公式=MAX(IF(OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$65536))=A2,OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B$2:$B$65536))))",按CTRL+SHIFT+ENTER结束,之后向下填充公式.

我在监测一个点的电压值，每天正点记录一次，这样每天就产生了24个值，现要进行一个月，共产生了720个值。根据这720个值，用EXCEL作一个图表，横坐标是时间，单位是----天，纵坐标是测量值。难点：横坐标每个刻度为一天，如设分类轴刻度线之间的分类数为24的话，横坐标每个刻度下显示为：12549，而其单位为天，那不就成了第1天过了，就到了第25天？如设分类轴刻度线之间的分类数为1的话，可是我每天有24个值？

解答：我试验了一下，发现还是能够解决的。横坐标为时间，每天24小时，一个月30天，共计720个数据，纵坐标为数值，这样做出来的表格非常长，数据这样排，第一列（0小时，1小时，23小时，0小时，1小时,..23小时，）这样总共重复30天，第二列为每天24个数据，做折线图（第四种），先按照向导一步步做下去，删除第一个系列，就可以了。做出来的图像横坐标（分类轴）要调整，刻度调整为标签间的分类数为1，就可以达到目的！剩下可以手工在每天的开头处加上日期（可以用文本框），不知道这样解决可以吗？

解答：选定图表部分，复制它，打开可以编辑jpg或gif格式的软件，然后粘贴保存为jpg或gif格式文件即可。

解答：不要用工具条上的显示小数位数来控制小数位数，它只是显示这几位，实际还是按原来的数计算。如：1.2345，你显示两位，为1.23，而实际他是按1.2345去计算的，要用round函数，round(数据,2)即可。如果怕繁的话，可在选项里设置按显示计算，呵呵，试试看。

解答2：我是做财务工作的，这一点有较深的体会

就是无论用什么函数都要在最外面加上round(),不然的话因为那一两分钱定会被领导狠狠说一顿

再有两条经验

1、如果准备作大一些的表格，尽量不要使用"合并单元格"以免以后使用函数麻烦；

2、要分清你作的表格主要是使用来存储数据，还是用来打印输出，如果想两者兼顾，小一点的表格还可以，大了就不好办了。

比如说现在Sheet2!a1引用Sheet1!a1,Sheet3!a1引用Sheet2!a1，那么有什么办法可以表示Sheet(n)!a1引用Sheet(n-1)!a1。我是Execl方面的菜鸟，希望各位大虾能给我解决的方法或者思路也行啊。

解答1：用(n).Cells(a,1)=Sheets(n-1).Cells(a,1)

又问：="=SUM(RC[-2]:RC[-1],_______!R[-1]C)".在_____处，我应该怎么填，才可以得到上一张表格？

答：在空格处写上：sheets().注意如果当前工作表是第一张（即＝0）时会出错。

或：=INDIRECT("sheet"A1"!$A$1")

有这样一个问题，学校里共有24个科目（每个科目都有其代码，每门科目都有它相应的教材费），但学生最多只能选其中４门科目，那么我在一个表里每个记录中存储学生所选的４门科目的代码，并要在该表里汇总４门科目的总教材费，怎么汇总。（各门科目代码及对应的教材费存在另一个表里）。

解答：你可以这样：

1，表2：

序号科目教材费/1语文120/2数学100/3英语150/4政治70/5历史110/6地理90/7物理250/8化学180

2，表1：

姓名科目1科目2科目3科目4教材费合计/赵1234440*/钱2345430/孙3456420/李4567520/张5678630.其中教材费单元格(打*号处，即F2)公式如下：

=VLOOKUP(B2,Sheet2!A:C,3)+VLOOKUP(C2,Sheet2!A:C,3)+VLOOKUP(D2,Sheet2!A:C,3)+VLOOKUP(E2,Sheet2!A:C,3)。其下单元格复制公式。

解答：我在excel中试验了一下，发现票面分解还是比较简单的，下面是我的做法：

ABCD

1票面金额655389.26/2面值张数余额/31006553（公式为：int(b1/a3)89.26（公式为：b1-c3*a3)/4501（公式为：int(d3/a4)39.26（公式为：D3-A4*C4)/520119.26/61019.26/7514.26/8220.26/9100.26/100.500.26/110.210.06/120.100.06/0.0510.01/0.0200.01/0.0110.00/第五行以下的数据就不需要一一输入了，选中C列和D列(C4：D15)，按ctrl+D,结果就出来了。

打开Excel工作表，建立如下表格所示的表格：

ABCDEFGHIJKLM

姓名性别生日年龄年级语文数学外语政治平均工资奖励总额

王明男289989287

1设置生日为11-28-1998的形式，并根据生日计算出学生的年龄。/2对平均分=90分，80=平均分90，60=平均分80的奖励金额数放在单元格N5..N7中，请利用公式计算出每位同学赢得的奖金。/31年级学生工资为80元，2年级工资为100元，3年级工资为110元，请计算出每位同学的工资数，并根据工资数和奖励数计算每位同学的工资总额。

解答：1.计算年龄:=INT(((YEAR(NOW())-YEAR(C2))*12+MONTH(NOW())-MONTH(C2))/12)(注意学生生日在C列)

2.计算工资:=IF(LEN(E2)0,CHOOSE(E2,80,100,110),"")(注意学生年级在E列)

3.奖励:=IF(J2=90,$N$5,IF(J2=80,$N$6,IF(J2=60,$N$7,"")))(平均分在J列)

若有20列（只有一行），需没间隔3列求和，该公式如何做？前面行跳跃求和的公式不管用。

解答：假设a1至t1为数据（共有20列），在任意单元格中输入公式：=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1))/按ctrl＋shift＋enter结束即可求出每隔三行之和。

打比方说有一个数字123456，我只想取它的后三位，也就是说只需要456，应该用什么函数。

解答：假设A1的值为123456,用=RIGHT(a1,3)可以取其后三位./你如果想取其中的几位，不妨试试mid函数。

00020304T02390400020304T0239/00020404T02110400020404T0211/00020604T01990400020604T0199/00020704T02160400020704T0216/00021304T02410400021304T0241/00021404T02220400021404T0222/00021504T02220400021504T0222/00021704T004T0139/就像上表一样，怎样用第一列的数据形成第二列的数据，即在特定位置加上几个相同字符串？

解答：b1="04"a1

问：哪如果倒过来呢？

答：用公式A1=MID(B1,3,13)或A1=SUBSTITUTE(B1,"04","",1)

解答：假设你要以Sheet1的A1单元格中的值为文件名保存，则应用命令：

(Range("Sheet1!A1"))+".xls"

A1是文本串的话，使用这条命令：("A1")".xls"。当然，如果你的A1是路径，F1是文件名的话，可以这样写：("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)

解答：1、选定你要限制输入的列（假设是A2:A20），选菜单的"数据"－"有效性"；

2、在许可条件中选定"自定义"，在公式一拦中，输入你要求的限制，例如："=COUNTIF($A$2:$A$20,A2)=1"。

3、你还可以在"输入信息"和"出错信息"输入一些提示信息。

不过，你一定要注意！这个功能只能在你从键盘上键入数据时有效！拷贝和粘贴数据的操作是无效的。

我输入123456.52它自动给拆成￥12345652的形式并且随我输入的长度改变而改变？

解答：我所知函数不多，我是这样做的，如有更方便的方法，请指点

例如：

在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

行112345/行212345/行312345/以下类同…/…

2.改为行排列（每12行5列内容共12组，排为一行）

列1列2列3列4列5列6列7列8列9列10……(60列)

原1-12行1234512345……(60列)/原13-24行1234512345……(60列)/原25-36行1234512345……(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转换()

DimnumcolAsInteger

DimnumrowAsLong

DimiAsLong

DimxAsInteger

DimnumperrowAsInteger

numperrow=InputBox("请输入每行要填的数据行的数目:")

Range("数据").Select

numrow='数据区的行数

numcol='数据区的列数

x=numperrow*numcol

Range("a1").Select

Fori=1Tonumrow'以数据的每一行为单位进行剪切

Range("数据").Rows(i).Cut

(,numcol).Select

If(iModnumperrow)Then'判断是否要换行

Else:(1,-x).Select

If

Nexti

Sub

'本程序需要把要变换的数据命名为"数据"(数据区的列数可以任意,不一定是5)方法:选中该区域,在名称框内输入"数据"即可,然后按程序要求输入每行要填的数据行的数目(如本例中的12)

解答：Sub自动筛选()

OnErrorGoToxx

=True

xx:

Sub

本人有以下一个问题需要解决，请诸位大虾指点迷津。

如下表：

ABC

150采购（采购汇总）/260工程（工程汇总）/380工资（工资汇总）/4100税（税汇总）/570采购/650工资/760工程//请问：C1、C2、C3、C4中的公式如何设置？

解答：方法1：如下表：

ABC

150采购=countif(a1:a7,"采购")/260工程=countif(a1:a7,"工程")/380工资=countif(a1:a7,"工资")/4100税=countif(a1:a7,"税")/570采购/650工资/760工程/

方法2：用如下方法才对：

ABC

150采购=SUMIF(B1:B7,B1,A1:A7)

260工程=SUMIF(B1:B7,B2,A1:A7)/380工资=SUMIF(B1:B7,B3,A1:A7)/4100税=SUMIF(B1:B7,B4,A1:A7)/570采购/650工资/760工程

方法3：由于Ｂ栏属于数据表的一部分，可能会随时改变，故此最好把采购等项目名称抄到另外一个地方（Ｃ栏）。

ABCD

150采购采购=SUMIF(B$1:B$7,C1,A$1:A$7)/260工程工程=SUMIF(B$1:B$7,C2,A$1:A$7)/380工资工资=SUMIF(B$1:B$7,C3,A$1:A$7)/4100税税=SUMIF(B$1:B$7,C4,A$1:A$7)/570采购/650工资/760工程/还有，记着要适当地使用绝对参照地址(Absolutereferencing)。

方法4：

ABCD

150采购采购=SUMIF(B:B,C1,A:A)/260工程工程=SUMIF(B:B,C2,A:A)/380工资工资=SUMIF(B:B,C3,A:A)/4100税税=SUMIF(B:B,C4,A1:A)/570采购/650工资/760工

我想编的公式是：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)

解答：1：if(and(a=0,b=0),"错误","正确")

2：=if(a10,if(b1=0,"错误","正确"),"条件一不满足")

=IF(B3C1,IF(SUM(B3:B4)C1,IF(SUM(B3:B5)C1,IF(SUM(B3:B6)B1,IF(SUM(B3:B7)C1,"outofrange",(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)找到结存批次。然后计算结存平均单价及发出平均单价。

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

或：=SUMPRODUCT(A1:A10,B1:B10)

解答：转成英文：

=TEXT(WEEKDAY(A1),"dddd")

转成中文：

=TEXT(WEEKDAY(A1),"aaaa")

我现用VLOOKUP（221，A1：E33，5，0）这一公式引用其同行第5列的数值时可以使用，但当查询到没有221时，想使其返回值为"0"不知如何处理。

解答：=if(iserror(vlookup())=true,0,vlookup())

解答：目前自定义函数是不能做到完全象内置函数一样的。但是可以给它添加一段说明。

打开带有自定义函数的工作簿-〉按快捷键Alt+F8—〉在光标处输入函数名字—〉单击"选项"—〉输入说明文字。

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

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

我想在sheet1中输入数据，例如我先在A1中输入1，确定是输入第一个单位的数据，然后从A2开始是第一个单位的数据。输入完成后，在SHEET2中的第一个单位数据就显示出来了，我的问题是，我想用SHEET1表再输入第二个单位的数据，但是这时SHEET2中第一个单位的数据没有了，显示是第二个单位的数据，如何才能保存第一个单位的数据，又能用SHEET1再输入第二个、第三个单位的数据？

解答：=IF(户汇总!$B$31,"",户汇总!B5)

A列B列C列

1a

2a

3a

1b

4b

我想当B2=B3，而且A2=1时，C2的值为OK，请问该如何写这个公式？

解答：=IF(AND(B2=B3,A2=1),"OK","")

解答：可使用Workbook的Before_Print事件.

按[Alt+F11]进入VBEditor,

左侧会有一个[ThisWorkbook],

(如果没有,按[Ctrl+R]叫出)

在[ThisWorkbook]快按两下,贴上程序--

PrivateSubWorkbook_BeforePrint(CancelAsBoolean)

[A1]=[A1]+1

Sub

假如，现在A1，A2，A3;B1,B2,B3

只要B1不为空B1＜＞""，那么B2＝A2B3＝A3

如何使用其IF语句来实现？？

解答：B2=IF($B$1"",$A2)

B3=IF($B$1"",$A3)

对于工作表中的数据库,随便举一例:

编号数量1数量2

0118080

0219090

0117080

0116080

我如何计算(数量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小东909090270/2明明959290277/3小英968991276/4小刘959092277/5小红959192278/要在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，比较好。

ABC

4278427/6128612/9248924/2216409/9416/62016/95516/4098/请问：假定有如上两列数据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的内容

abc

姓名工号部门

张三100mm/李四101mm2/.

表sheet2

abc

姓名部门/李四/。。。

要在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)

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

序号姓名性别年龄

1李1男30/2李2女25/3李3女22/4李4男31/：：：：/99李99男28

解答：在"序列"与"姓名"之间插入一个空列，在其第一格输入"条件"。在"条件"下面输入公式"=IF(MOD(A5,2)=1,"单数","双数")"，用"自动筛选"。

就是说每增加一行，就会自动计算n那一列的值、多谢！

解答：1：我有个笨方法，须用VBA，不知和你意否。

PrivateSubWorksheet_Change()

(,14).Formula=""ThenCells(,14).Formula="=SUM(RC[-12]:RC[-1])"

Sub

2：假设需要被SUM的数据存放在B列(第一个数据存放在单元格B2),将以下公式COPY到你希望显示结果的单元格中:

=IF(LEN(B2)0,SUM(B$2:INDEX(B:B,ROW(),1)),"")

切记如果B列中有空单元格隔开了需要计算的数据,必须以零填充该单元格.

3：这个问题不用想得很复杂，就是要把n2单元格输入公式后，拖曳向下复制公式。

也许是我想得太简单了。

解答：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)

有一个表格：

承兑行天数实际天数

深圳工行98101

广州交行105108

鞍山工行100100

如果"承兑行"一栏含有鞍山字样，则"实际天数"等于"天数"；如果"承兑行"一栏不含有鞍山字样，则"实际天数"等于"天数"加上3，请问：如何设置？

解答：这个问题的重点在于如何判断是否含有"鞍山"字样。

如果"鞍山"在字符串中的位置是固定的，则很容易用MID函数找到并进行判断；

如果不是，则需要用FIND或Search函数来找到再判断。

或：如果A列中是承兑行，B列是天数，C是.

可以试用一下以下的公式：

=IF(ISERROR(FIND("鞍山",A2)),B2+3,B2)

解答：=INT(D2/10)*100，当然，你的单元格格式设置成$格式就可以了。否则用，="$"INT(D2/10)*100

则该单元格成字符型。当然，你也可以用IF函数，但它有7层的限制。if(D230,"300",IF(D220,"200"))

有两张工作表，内容都是电话号码、用户名称，怎样对两张工作表进行比较？（把第一张工作表和第二张工作表中电话号码相同的项目复制到新工作表中或加上标记）

解答：先备份工作簿。

假设SHEET1、SHEET2两张表的结构相同，A列为用户名，数据由第二行开始。新建SHEET3并在其A2单元格中输入=IF(ISNA(MATCH(Sheet2!$A2,Sheet1!$A:$A,0)),"DELETTHISROW",Sheet2!A2)并向下、向右填充。

完成后SHEET3中多了SHEET2和SHEET1共有的用户名，以及由"DELETTHISROW"填充的数据行。剩下的就简单了！

解答：统计F4到F62的偶数单元格合计数值。公式一｛=SUM(IF(MOD(ROW(F4:F62),2)=0,F4:F62))｝

公式二(要有安装'分析工具箱')｛=SUM(IF(ISEVEN(ROW(F4:F62)),F4:F62))｝

有两个工作表，工作表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为精确匹配。

如：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批注()

Fori=1To8

OnErrorResumeNext

a=Sheets(1).Cells(i,1).

=91Then

Sheets(1).Cells(i,2)="左侧单元格无批注"

Sheets(1).Cells(i,1).AddCommentText:="请输入批注内容"

Else

Sheets(1).Cells(i,2)="左侧单元格批注"a

If

Nexti

Sub

我用了公式却不行.用Range("A2").value=Range("Range("A3").value").value也不行.(A3有公式:="sheet"A3"!E1")请各位帮忙了.

解答：if(a1=1,sheet1!a1,if(a1=2,sheet2!a1,if(a1=3,sheet3!a1)))

比如说第一行的第一个单元格=1那么就自动隐藏第一行？

解答用vba。

Sub隐藏()

fori=1toxx--行号

ifsheets(1).cells(i,1)=1then

Rows(i).Select

=True

if

nexti

解答：A1中输入，B1中转换。。B1=Left(A1,4)"—"MID(A1,5,2)"—"MID(A1,7,2)""MID(A1,9,2)":"MID(A1,11,2)":"MID(A1,13,2)

解答：方法一：公式

可在A1储存格输入以下公式，再行拖曳至A200即可。

=INDIRECT("Sheet"ROW()"!A19")

方法二：VBA

SubMacro1()

'选择工作表sheet0

Sheets("sheet0").Select

Forr=1To200

'将工作表1~200里面的D17复制到sheet0的A1~A200

Cells(r,1)=Worksheets(CStr(r)).Range("D17")

OnErrorResumeNext

Nextr

Sub

我只能做到新建一列，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，其倒数为1，Sum统计计1

如果出现N次，其倒数为1/N，出现了N次，求和就是Nx1/N，最后Sum统计就只计1。

解答：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)}

中行410091252、青泥支行200303004500696、卡伦办事处801017651、站前支行0709000309221004055、金州支行08091

解答：1、用函数可以解决。假如A1为中行410091252

则B1=LEFT(A1,(SEARCHB("?",A1,1)-1)/2)、C1=MID(A1,LEN(B1)+1,50)、随后将B1、C1的公式往下复制。

2、Sub分列()

(1).Cells

Fori=1ToLen()

IfAbs(Asc(Mid(,i,1)))256Then

(,(+1)).Formula="'"Right(,(Len()-i+1))

=Left(,(i-1))

ExitFor

If

Nexti

Next

Sub

3、是否将Asc(Mid(,i,1))256改成47Asc(Mid(,i,1))58会更好，因为它只将数字抽出，如果数字前有英文字的话将抽往数字列。

4、我看帮助中instr函数不错

配合循环速度应快些

我有一个表格，有一列数据是以6个单元格为一个单位做一次小计，我在小计中加入了公式

但是我还想求一下这个小计的总合，我想加入一个公式，但是我有SUM到了50多个时就加不进去了，用加号连加也不行，不知这个问题怎么解决

解答：用SubTotal()解决即可

解答：PrivateSubWorksheet_Change(ByValTargetAsRange)

=()

Sub

解答：=DATEDIF(A1,B1,"d")

问：我用了datedif发现一个问题、即当A列B列时,结果正确,而当A列B列时出错了、请问用什么办法解决

答：=IF(A1B1,DATEDIF(B1,A1,"d"),DATEDIF(A1,B1,"d"))

解答：Excel2000

数据取得外部数据新增数据库查询ExcelFile*找到档案

[选项]勾选所有选项会找到所有未命名[区块]如Sheet1$

找到字段其它跟着查询精灵导引一步一步作

此中间层组件叫作MicrosoftQuery

可以用来拯救[毁损档案]

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

解答：submytime

range("a1")=now()

+TimeValue("00:00:01"),"mytime"

sub

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

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同时使用条件B10和B110

解答：and(B10,B110)

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

我在编写程序时遇到这样一个问题,每次用循环程序时向单元格写或读数据时总是面向一个固定区域,如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

好了,我想不用我多说你就会制出跟实际表格一模一样的表格来.

解答：1、如果A1＝1008在B1中输入＝if(right(a1,1)=8,a1+1,a1),可以实现如果结尾是8加1，不是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.12123.00

写字间收入147,258.00147.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))

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