excelVBA集锦

引用问题 我在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是4 B4=A“&C4+C5 ,同①。 如何在一列列出工作薄中的所有表(表名无规律) 以下宏将在a列传回所有工作表名称。(很实用) Sub Maco1() For i = 1 To Sheets.Count Cells(i, 1) = Sheets(i).Name Next End Sub 18位身份证号码输入的几种解决方法 相信在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算出数值大小在50~60之间的单元格个数 解答：①你可以综合一下呀! =countif(a1:a9,>=50)-countif(a1:a9,>60) ②{=SUM((a1:a9<60)*(a1:a9>50))} ③为什么{=SUM((a1:a9<60)*(a1:a9>50))}可以求出符合条件的个数，按理后面应再加一待求和区域如B1:B9等等，不知数组计算的内部机制是怎样的，不知谁能给个解释,谢谢！！ ④这个数组公式是这样运算的： a1:a9区域的数值分别与60比较。假如a1:a9的数依次为15,25,35,45,55,65,75,85,95.那么(a1:a9<60)返回{true,true,true,true,true,false,false,false,false}，同理(a1:a9>50)返回｛false,false,false,false,true,true,true,true,true}。然后(a1:a9<60)*(a1:a9>50)则返回{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))*滞纳金率（每天）*应收金额 SUM函数难题 A B 234 230   12    10 13  如何用函数计算出下面几个数据 1。A和B都有数据且A>B的个数 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 工作表中的小写字母都变成大写 解答：请运行以下程序：(测试通过) Sub ConvertToUpperCase() Dim Rng As Range Worksheets(Sheet1).UsedRange.Select For Each Rng In Selection.Cells If Rng.HasFormula = False Then Rng.value = UCase(Rng.value) End If Next Rng End Sub 用COUNTIF计算整个B列中含有“F”这个字符的单元格的个数 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 的话，横坐标每个刻度下显示为：1 25 49，而其单位为天，那不就成了第1天过了，就到了第25天？如设分类轴刻度线之间的分类数为1的话，可是我每天有24个值？ 解答：我试验了一下，发现还是能够解决的。横坐标为时间，每天24小时，一个月30天，共计720个数据，纵坐标为数值，这样做出来的表格非常长，数据这样排，第一列（0小时，1小时，......23小时，0小时，1小时,.....23小时，）这样总共重复30天，第二列为每天24个数据，做折线图（第四种），先按照向导一步步做下去，删除第一个系列，就可以了。做出来的图像横坐标（分类轴）要调整，刻度调整为标签间的分类数为1，就可以达到目的！剩下可以手工在每天的开头处加上日期（可以用文本框），不知道这样解决可以吗？ 请问如何把在Excel中绘制的曲线图表另存为jpg或gif格式的图片 解答：选定图表部分，复制它，打开可以编辑jpg或gif格式的软件，然后粘贴保存为jpg或gif格式文件即可。 round函数 解答：不要用工具条上的显示小数位数来控制小数位数，它只是显示这几位，实际还是按原来的数计算。如：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：用VBA.Sheets(n).Cells(a, 1) =Sheets(n-1).Cells(a, 1) 又问：ActiveCell.FormulaR1C1 = =SUM(RC[-2]:RC[-1],_______!R[-1]C) .在_____处，我应该怎么填，才可以得到上一张表格？ 答：在空格处写上： sheets(activesheet.index-1) .注意如果当前工作表是第一张（即 activesheet.index-1＝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 教材费合计/赵 1 2 3 4 440* /钱 2 3 4 5 430 /孙 3 4 5 6 420 /李 4 5 6 7 520 /张 5 6 7 8 630 .其中教材费单元格(打*号处，即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) 。其下单元格复制公式。 在EXCEL2000中可否实现票面分解 解答：我在excel 中试验了一下，发现票面分解还是比较简单的，下面是我的做法： A B C D 1 票面金额 655389.26 /2 面值 张数 余额 /3 100 6553（公式为：int(b1/a3) 89.26（公式为：b1-c3*a3) /4 50 1（公式为：int(d3/a4) 39.26（公式为：D3-A4*C4) /5 20 1 19.26 /6 10 1 9.26 /7 5 1 4.26 /8 2 2 0.26 /9 1 0 0.26 /10 0.5 0 0.26 /11 0.2 1 0.06 /12 0.1 0 0.06 /0.05 1 0.01 /0.02 0 0.01 /0.01 1 0.00 /第五行以下的数据就不需要一一输入了，选中C列和D列(C4：D15)，按ctrl+D,结果就出来了。 年龄及奖学金问题 打开Excel工作表，建立如下表格所示的表格： A B C D E F G H I J K L M 姓名 性别 生日 年龄 年级 语文 数学 外语 政治 平均 工资 奖励 总额 王明 男 　 　 2 89 98 92 87 　 　 1 设置生日为11-28-1998的形式，并根据生日计算出学生的年龄。 /2 对平均分>=90分，80<=平均分<90，60<=平均分<80的奖励金额数放在单元格N5..N7中，请利用公式计算出每位同学赢得的奖金。 /3 1年级学生工资为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结束即可求出每隔三行之和。 在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(B31,,户汇总!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 根据A1的内容,决定A2的数值是来自sheet1,sheet2还是sheet3. 我用了公式却不行.用 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 隐藏() for i=1 to x x--行号 if sheets(1).cells(i,1)=1 then Rows(i).Select Selection.EntireRow.Hidden = True end if next i 直接输入一组数如“20020213101235”后，自动转换成日期格式 解答：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) 把sheet1到sheet200的a19这一格，依序贴到sheet0的a1到a200 解答：方法一：公式 可在A1储存格输入以下公式，再行拖曳至A200即可。 =INDIRECT(Sheet&ROW()&!A19) 方法二：VBA Sub Macro1() '选择工作表 sheet0 Sheets(sheet0).Select For r = 1 To 200 '将工作表 1~200 里面的 D17 复制到 sheet0 的 A1~A200 Cells(r, 1) = Worksheets(CStr(r)).Range(D17) On Error Resume Next Next r End Sub  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，其倒数为1，Sum统计计1 如果出现 N 次，其倒数为1/N，出现了N次，求和就是Nx1/N，最后Sum统计就只计1。 如果有文本串YY0115，我想取第三、四的值01，应该用什么函数 解答：1、=mid(YY0115,3,2)& 2