如何实现对日期格式的要求条件
Q:在条件语句中如何实现符合某个时期的条件的记录进行统计,比如有1-12月份的记录单,需要实现对每个月里些数据的统计汇总/(格式如何?)
A: dongmu
以下公式,A列为日期列,B列为数据,要求计算1月份的累计:
=SUM(IF(MONTH(A:A)=1,B:B,0))
此为数组公式,在输完公式后,不要ENTER,而要CTRL+SHIFT+ENTER.(好象不足呀!程香宙)
截取单元格里某个字符后的字符
A: tof :使用RIGET()或LEFT()函数就可以了,详细可以参考EXCEL的帮助
liberty:比如这样:
a1内容为dfgsd2163.com,g2er.g
我需要提取出,号以后的字符,g2er.g
午餐 :先使用SERACH或FIND函数找出“,”的位置,再用MID来取数,如A1=123,456,我想取出“,”以右的数,可以=MID(SEARCH(,),8)。可能我用的函数不行,不过思路一定行。
你多看看帮助文件,我在网吧,此机无EXCEL无法试,不过我以前用过,用MID加SEARCH一定行的通,多试试吧。
实在不行再给我邮箱发信了,愿意帮忙!祝你好运。
我认为能用函数解决的最好不用VBA,你说呢?
markxg :假设A1中有dfgsd2163.com,g2er.g
=RIGHT(A1,LEN(A1)-SEARCH(",",A1,1))
missle:我今天也试着用公式解决了你的问题,思路与MARK的一样,只是他用的是SEARCH,我用的是FIND。但不是很清楚你的问题,是否要把“,”号一起给截取下来,如果是的话:公式应该是=RIGHT(A1,LEN(A1)-SEARCH(",",A1,1)+1),否则“,”号是无法截取到的。
把问题给纵深一下,如果单元格内有多个“,”的话,如A1中是:aa,bb,cc,dd 我想截取 cc,dd 该如何作呢?或者想知道这个单元格中含有多少个“,”又应该如何呢?(VBA的解决方法除外)
午餐 :Missle,你看仔细了,在Search函数中是有参数的,它允许你指定在第几个出现显出位置,如“,”你可以通过指定search参数选定第几次出现时的位置,对于截取我更认为MID比LEFT和RIGHT好
用if函数判断
请问:如何用if函数判断,如果a1单元格大于0,b1单元格为0是错误,为非0是正确?
A: dongmu
if(and(a<=0,b=0),"错误","正确")
葡萄 :=if(a1>0,if(b1=0,"错误","正确"),"条件一不满足")
分别显示总分最高的同学的班级、姓名、总分
Q:问题1是这样的:
1、在A1:A30单元格区域中输入500至600之间的数值;
2、在B1单元格中输入500;
3、在C1单元格中插入公式: COUNTIF(A1:A30,">=INDEX(B1,1)*0.9")
回车后C1单元格中显示的结果是0,为何不能把A1:A30中输入的数大于或等于500*0.9的单元格个数统计出来?错在哪里,该如何更正?
问题2有下面一个表格:
班级 姓名 政治 语文 数学 总分
1 小东 90 90 90 270 /2 明明 95 92 90 277 /3 小英 96 89 91 276 /要在K1:K3的单元格中分别显示总分最高的同学的班级、姓名、总分 .该如何设置公式?
答案1:建议你好好看看COUNTIF的帮助,真是搞不明白,为什么在B1里输入500而不是450呢? COUNTIF不支持一个以上的条件,你应该用DCOUNT。
答案2:
K1=INDEX(A2:F4,MATCH(K3,F2:F4,0),1)
K2=INDEX(A2:F4,MATCH(K3,F2:F4,0),2)
K3=MAX(F2:F4)
解答3:
问题一:公式改为:=COUNTIF(A1:A30,">=" & INDEX(B1,1)*0.9)
问题二:在K1:K3中分别输入如下公式(假设A1为"班级"):
=LOOKUP($K$3,$F$2:$F$4,A2:A4)
=LOOKUP($K$3,$F$2:$F$4,B2:B4)
=LARGE(F2:F4,1)
解答4
第一题:=COUNTIF(A1:A30,">="&B1*0.9)
第二题:K1==INDIRECT("A"&MATCH(MAX(F1:F4),F1:F4))
K2=INDIRECT("B"&MATCH(MAX(F1:F4),F1:F4))
K3=MAX(F2:F4)
定有如上两列数据A和B 现在想要统计满足条件B=8的 并且在C列自动生成数据
Q: 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列自动生成数据,我不懂公式怎么写?我知道筛选能够做到 ,但是由于数据量比较大 ,想做一个模板,免去一些重复劳动 .
A: dongmu
=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)))
老夏 L:
B栏有100个选取条件,公式要修改100次 ,有甚么灵活性?,给字段名称,插入头两列.B1=SUBTOTAL(9,B4:B65536) .解决掉所有问题 ,控件找自动筛选.
排名问题
在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分的有两人,不要出现第二名与第三名都是"明明"的结果.
A: dongmu
定义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名的情况了。该公式可以无限排列。
研究彩票,从统计入手
Q:我有一个VBA编程的问题向你请教。麻烦你帮助编一个。我一定厚谢。
有一个数组列在EXCEL中如: 01 02 03 04 05 06 07 /和01 04 12 19 25 26 32 /02 08 15 16 18 24 28 /01 02 07 09 12 15 22/09 15 17 20 22 29 32/比较,如果有相同的数就在第八位记一个数。如 :01 04 12 19 25 26 32 2 /02 08 15 16 18 24 28 1 /01 02 07 09 12 15 22 2 /09 15 17 20 22 29 32 0 .这个数列有几千组,只要求比较出有几位相同就行。
解答:把“01 02 03 04 05 06 07 ”放在表格的第一行,“01 04 12 19 25 26 32 2”放第二行。
把以下公式贴到第二行第八个单元格“A9”中,按F2,再按CTRL+SHIFT+ENTER.
=COUNT(MATCH(A2:G2,$A$1:$G$1,0))
去掉XLS文件头上菜单栏的“MIcrosoft EXCEL ”字样
A: Private Sub Workbook_Open()
Application.Caption = "程香宙专用表格"
End Sub
替换数据
Q:请教各位如何用将一组数据,
如:6550894, 9852547, 2656032, 7461136, 0505867, 5564892, 7235580,0421077,我需要把数据中的数字1,3,5换为符号A表示,2,4,6换为符号B表示,依此类推将数据中的阿拉伯数字0~9分为几类用其它符号替换。
A: leaf
用VBA处理比较方便。 只用EXCEL函数,感觉代价太大。
假设:B13值为9550894在B14中输入=IF(ISERROR(FIND(MID($B$13,1,1),"135")),IF(ISERROR(FIND(MID($B$13,1,1),"246")),IF(ISERROR(FIND(MID($B$13,1,1),"79")),IF(ISERROR(FIND(MID($B$13,1,1),"80")),"","D"),"C"),"B"),"A"),C14中MID()第二个参数为2,以此类推...最后在目标单元格中输入:=CONCATENATE(B14,C14,D14,E14,F14,G14,H14)
dongmu :表一:
AB...
11234567890 、2ABABABCCCD 、=SUBSTITUTE(A4,A$1,A$2)
说明:先列一个替换表,如表一,在A4处填如数据,在B4处填如上述公式=SUBSTITUTE(A4,A$1,A$2),并向右拖动9个同样的公式,最后一个便是结果.在将该10个相同的公式向下拖,便得到其它的结果.好处:可以修改表一,产生变化.
ACCESS :你可以把全部数据拷贝到WORD中,再用替换命令,想怎么换就怎么换,然后在拷贝回来。
复制数据再转置,不复制转置被隐藏的行或列
解答:选择需要转置的单元区域,按下F5-->定位条件-->可见单元格-->复制-->选择性粘贴-->转置。
如何始终打开默认的工作表
可不可以作到每次保存工作表时,无论保存时是在哪一个
SHEET,但是当下次再打开时,还是原来默认的那张工作表。比如SHEET1。谢谢!
解答:Private Sub Workbook_Open()
Worksheets("sheet1").Activate
End Sub
如何分割文本
有一列数据,全部是邮箱的,现在想将@前面的账号与@后面的域名分割开,分为两列,如何做?
解答:采用函数分割:例如:A1:
name@163.comB1:=LEFT(A1,FIND("@",A1)-1) --> name
C1:=RIGHT(A1,LEN(A1)-FIND("@",A1)) --> 163.com
或:数据-分列-分列-分隔符号-@就可以了
两列合一列
现有两列数据A列与B列,我想把B列的数据合并到A列但必须是B1单元格的数放到A1的下面,B2放到A2的下面依此类推,有什么办法呢?
解答:=INDIRECT("r"&INT((ROW()+1)/2)& "c"&MOD(ROW()+1,2)+1,0)
解释:
(一)EXCEL表中的列、行样式有两种:
一种标记样式为:
列(字段)以A,B,C,D......
行(记录)以数值1,2,3,4,5......
第一列第一行的单元格为A1
另一种标记样式为(取ROW和COLUMN的首位字母):
列(字段)以R1,R2,R3,R4,R5......
行(记录)以C1,C2,C3,C4,C5......
第一列第一行的单元格为R1C1
(二)请参阅INDIRECT函数的帮助说明!!!
公式:
=INDIRECT("r"&INT((ROW()+1)/2)& "c"&MOD(ROW()+1,2)+1,0)
等同于:
=INDIRECT("r"&INT((ROW()+1)/2)& "c"&MOD(ROW()+1,2)+1,FALSE)
(三)工具-->选项-->常规-->设置,还可选取R1C1引用样式
每次清除数据时能否作到跳过隐藏的单元格
解答:F5----定位条件----常量----确定----Del
或:F5->定位条件->可见单元格->确定->DEL
也就是单击Sheet2时,在Sheet1的A列的最后一个记录的下一行自动填上“End”
在sheet2:
Private Sub Worksheet_Activate()
dim i as integer
i = Sheets("Sheet1").Cells(1, 1).CurrentRegion.Rows.Count
Sheets("Sheet1").Cells(i + 1, 1) = "End"
End Sub
用函数将输入的月份转换为这个月所包含的天数
假设A1单元格为月份:
=TEXT((DATE(YEAR(NOW()),A1+1,1)-1),"d")
或:=DAY(DATE(YEAR(NOW()),A1+1,0))
介绍经验:就SUM函数来讲,以下动态地址可行
1.SUM($A$1:A2),SUM(A$1:A2)
2.B2="A9",
SUM(INDIRECT("a1:"&B2))
3.B1="A1",B2="A9"
SUM(INDIRECT(B1&":"&B2))
4.B1="A1:A9"
SUM(INDIRECT(B1))
5.SUM(INDIRECT("A1:"&"A"&ROW()-1))
6.SUM(INDIRECT("A1:"&ADDRESS(ROW()-1,COLUMN())))
在EXCEL中如何统计字数
用{=SUM(LEN(范围))}试试
如何自动填充内容
A1:A20是编号,B1:B20是姓名,C1:C20是性别,当我在A21单元格输入A1:A20范围内的任意一个编号时,B21出现对应的姓名,C21出现对应的性别。该如何做,请帮忙。
解答:B21单元格公式“=IF(A21=0,"",VLOOKUP(A21,A1:C20,2,FALSE))”;C21单元格公式“=IF(A21=0,"",VLOOKUP(A21,A1:C20,3,FALSE))”这个公式也适用于A列编号不排序的情况,如果升序的话会更简单一点。
问:以上公式中的'false'有什么用?能否省略?
答:false参数主要是用它以后在A列中的数据可以不是升序排列。不然如果A列不是升序排列,公式会出错的。
工作表的标签的字体和大小可以更改吗
答:在桌面上点右键─内容─外观,相关的设定都在此更改。
自定义格式的体会
在format cell的时候,选了custom后在格子里输入你想要的位数,不变的部分就照着打进去,会变得部分打0就好了,(用0占位)。
例如:你要打的数字是00715834123456,后6位是不定的,那你要打在格子里面00715834000000。这样如果你输入最后3位是012,那么会显示出00715834000012;如果你输入54321,那么会显示出00715834054321。
如果你会变得部分是在数字的中间,比如我的item#会是9690000001-0000002,后面的-0000002是不变的,那我就可以设置自定义格式为9690000000"-0000002",这样当我键入502的时候就会显示9690000502-0000002。
再次显示出被隐藏掉了的行(第1行)
1: 选中隐藏的上、下行,右击鼠标,选“取消隐藏”(作者注:此法可行)
2:Ctrl+A-----格式-----行-----取消隐藏(可以,能够一次显示所有隐藏的行或列)
3:另一法(工作表处于未保护状态):假如 A1 被隐藏了在名称框中键入A1,回车按 Ctrl+Shift+0 或 Ctrl+Shift+9(只显示选定的隐藏列或行)
4:光标移到行号 4 上部变成 上下箭头状, 按住了, 拖也要把它拖出来!(慢,不好操作)
5:选择整个工作表(点击左上角),然后再选择菜单中的行,选择最适合的行高,然后就OK!,同样可以把隐藏的列显示出来。(这个办法最好,能够一次显示所有隐藏的行或列)
如何定义有效数字
例:取两位有效数是从第一个不是零的数字起,取两位。0.0023666取两位有效数是0.0023 。0.2366取两位有效数是0.23。
解答:用函数可如下: =FLOOR(A1, SIGN(A1)*10^(INT(LOG(ABS(A1)))-1)), +/- 小数有效,0无效.
其它形式的数据, 自行扩展.
sheet1工作表的A1、A2、A3单元格分别链接到sheet2、sheet3、sheet4
解答:1、 =indirect("sheet"&row()+1&"!a1")《程香宙的解释:indirect是把文本变为单元格引用的函数row()是取当前行号。例如在a1输入该公式,则row()=1,公式里的值变为indirect("sheet2!a1"),跟=sheet2!a1同效,在a2输入该公式,则row()=2,公式里的值变为indirect("sheet3!a1")》
2、使用插入----超级链接----书签----(选择)----确定
用SUMIF函数进行条件求和,不限于一个条件时如何设置参数
例如:有一个表格登记面粉、米粉、糯米、梗米、绿豆、早米……等等的进出流水帐,如果对满足单一条件的如面粉、糯米、绿豆等分别求和是没有问题的,但如果要将同一类的求和,例如将糯米、梗米、早米的数值加在一起,应该怎么办?
解答:提供以下公式供参考,设A列为名称、B列为数量:
=SUMIF(A:A,"糯米",B:B)+SUMIF(A:A,"梗米",B:B)+SUMIF(A:A,"早米",B:B)
如B1:D1为求和条件项,即B1="糯米",C1="梗米",D1="早米",
上述公式还可改为:
=SUMIF(A:A,B1,B:B)+SUMIF(A:A,C1,B:B)+SUMIF(A:A,D1,B:B)
如何在excel中已有的数值前加零变成六位
比如说 25、369、1569等,操作后变成000025、000369、00156
解答:如果直接输入的话,可以在数值前面加“'”,如“'002020”;
如果处理现成的数据,或者从别处(比如从A1单元格)链接来的数据,可以用公式:
=RIGHT("00000"&A1,6)
如何提取工作表中的背景图片
解答:找个干净的地方, 去掉网纹等不需要的东西, PrintScreen 再编辑
绘制有三条斜线的表头
解答:1、用绘图工具画出斜线>>画方框>>内添加文字>>去边框
2、引用WORD中的,然后再复制过来就可以!
在A列有一组数据不是按照大小顺序排列在B列中排名
解答:方法1、将A列COPY到B列,再排序。
2、rank函数(=RANK(A2:A11,$A$2:$A$11,0)(假设数据在A2:A11单元格,下同)
3、使用contif函数进行排列“=countif(a$2:a$11,">"&a2)+1"
有无办法让B2所在行都呈红色字体
解答:假设你有一个B列和一个A1的值,你的目的是,如果B2=A1的话,整个B列都为红色显示!
设置如下:先选定整个b列,也就是在B列列标处单击(废话~^_^),选择格式-条件格式
出现条件格式对话框,单击左边的下拉列表,里面只有两项,单元格数值和公式,选中公式,右边就可以输入任何可以返回逻辑值的公式了。输入这个公式=($B$2=$A$1)。千万注意要用绝对引用,因为如果是相对的,excel又自作主张的一个一个判断了,就没有作用了。(绝对正确并且好用)
现有12个工作表,是12张发票,建立一个汇总表,将发票号和金额汇总显示在一张表里
(发票号和金额在每张表的相同位置).
解答:在A1输入 =INDIRECT("sheet"&ROW()&"!d3")
在B1输入 =INDIRECT("sheet"&ROW()&"!d10")
再选择A1:B1往下复制到第12行。
经验技巧
按“Ctrl+~”可以一次显示所有公式(而不是计算结果)。再按一次回到计算结果。(程香宙)
在一个不对称的区域中如(b1:G7)中找到A行一组数据中的某个数并自动变红
解答:其实也很简单,你只要选定你的b1:g7,设置它的条件格式为=(COUNTIF($A$1:$A$7,b1))
注意,b1为相对引用,这里输入所选区域的第一个取值,那样你的所选区域会自动填充.达到你要的效果。(好)
不借助第三列而直接用函数或公式一步得到sum(a2/b2,a3/b3,…)的结果
解答:输入=sum(a1:a100/b1:b100),按ctrl+shift+Enter。
请问要如何算出每个月有几个星期一、二、三….日
解答:为简单起见,表格需作一下调整,将 "星期日" 移到 C1,其后依次,这也符合规则(请参阅函数: WEEKDAY()). 。在 C2 键入数组公式: {=SUM(IF(WEEKDAY(DATE($A2,$B2,ROW(INDIRECT("$A$1:$A$" & DAY(DATE($A2,$B2+1,1)-1)))))=COLUMN()-2,1))},向右复制、向下复制。公式解释一点:ROW(INDIRECT("$A$1:$A$" & DAY(DATE($A2,$B2+1,1)-1)))实际上是从 1 号测试到本月的最后一天.如需要,公式可再作精简。
让隐藏的列或行不参预计算
解答:使用subtotal函数,详细用法参见帮助。
一次删完Excel里面多出很多的空白行
解答:1、用分面预览看看
2、用自动筛选然后删除
3、用自动筛选,选择一列用非空白,空白行就看不到了,打印也不会打出来。但是实际上还是在的,不算删除。或者用自动筛选选择空白将空白行全显出来一次删完也可以。
4、先插入一列,在这一列中输入自然数序列,然后以任一列排序,排序完后删除数据后面的空行,再以刚才输入的一列排序,排序后删除刚才插入的一列。
表1、表2分别有20个人的基本情况和其中10个人的名字,让表1的数据自动填充到表2
答:1、用lookup函数即可。要保证20人不重名;
2、假设表1的D列对应表2的E列。E2的公式:=VLOOKUP(B2,Sheet1!B:D,3,FALSE)
使用vlookup函数返回#N/A符号时将此符号用0或空格来代替
答:这样处理: =IF(ISNA(VLOOKUP(C13,A1:B10,2)),0,VLOOKUP(C13,A1:B10,2))
或:IF(ISERROR(vlookup(a1,e1:g10,2,0)),0,vlookup(a1,e1:g10,2,0))。
通过条件格式将小计和总计的行设为不同的颜色
答:输入=RIGHT(RC,1)="计";设定字体、边框、图案;确定。
复制隐藏后的表格到一个新表格中使被隐藏的内容不显示
答:crtl+g-选可见单位格-复制-粘贴。
如何将一个工作簿中的一个Sheet隐藏
答:1、选“格式”---“工作表”----“隐藏”
2、使用VBA这样隐藏后在使用工作表保护。
Alt+F11----Ctrl+G----出现立即执行窗口,在此窗口内执行
Sheet1.Visible = xlSheetVeryHidden
这样隐藏后sheet在格式---工作表----取消隐藏是看不见的。
问:方法2更好哦,如何恢复呢?
答:sheet1.Visible =xlSheetVisible
工具菜单与视图中的工具栏不同
屏蔽工具菜单宏
sub notool()
MenuBars(xlWorksheet).Menus("工具").Delete
end sub
解除屏蔽
sub yestool()
MenuBars(xlWorksheet).reset
end sub
Alt+F11 进入VBA 编辑、插入模块、将上面宏复制到模块、运行宏。OK
查找并填写符合条件的单元格内容
我在工作中需快速复制每行多个数据(单元格)中最小值所对应的“标题名”,如E6是C6:Y6中的最小值,所对应的标题是E5单元格“某某公司”,要将其(某某公司)复制到B6单元格中,以此类推的复制很多很多行的内容。如果是手工一个一个查找与复制,实在是太慢太笨了,能否使用一个简单的公式计算呢?
答:B6单元格"=INDEX(C$5:Y$5,MATCH(MIN(C6:Y6),C6:Y6,0))"
填写空白行
我有个同事在一张空白表依次输入数据,为了省事她把和上一格内容相同的的省略不输,输了近200行。后来又觉得不够正式,想把空白的地方补上。她来问我怎么办好。当然依次填充也行,但我觉得烦(如果有2000行怎么办呵呵)我想了一个不是办法的办法:在A列和B列旁各插入一列,现在就有ABCDE列,我在B2中复制了A2中的内容,然后在B3中输入公式:IF(A3=0,B2,A3),然后往上往下复制公式。这样就填满了。如法炮制D列后隐藏AB列感觉就可以了。可是也烦啊,谁有更好的办法?
答:1、Sub feifjeifjeifjeifjeifjiefjiejfiejf()
For i = 2 To ActiveSheet.Range("a1").CurrentRegion.Rows.Count
If IsEmpty(Cells(i, 1))
Then
Cells(i, 1).FormulaR1C1 = Cells(i - 1, 1).Value
End If
Next
End Sub
2、筛选出空白行,输入公式=INDIRECT("a"&ROW()-1),填充
制订下月计划并显示为
中文我在五月份做六月份的计划,为减少工作量和更改的麻烦,我做模板并使用了公式="计划期:"&YEAR(NOW())&"年"&(MONTH(NOW())+1)&"月",结果如A1所示 计划期:2002年6月 (现在的系统日期是2002年5月)。 如果我想自动得到如A2中的结果 计划期:二○○年六月 ,请问要如何做才行,我设置了单元格的日期格式还是不行。
解答:1、先设置单元格格式为"二○○二年六月"那种类型,然后用如下公式:=DATE(YEAR(NOW()),(MONTH(NOW())+1),20)就可以了。
2、使用这个函数吧! =EDATE(NOW(),1)。单元格格式应设置为:日期----一九九七年三月。
3、设置单元格格式为:[DBNum1]"计划期:"yyyy"年"m"月" ,然后直接输入日期值(如2002/11)即可。
输入公式也可以。如=today()+30,可以得到下个月的月份。
&的用法
有E44单元格,我希望 总计:=SUM(E45:E49) 就是想让它经过自动求和后在一个单元格内显示 总计:120 。
解答:有多种方法实现,详细如下:
1、="总计:"&sum(e45:e49)
2、把E44格式设为"总计:"#0.00;"总计:"-#0.00;"总计:"0.00;@
3、将E44单元格格式自定义为 "总计:"0.000 即可,方便对E44的引用计算
4、=CONCATENATE("合计:",SUM(e45:e49))
有5行数据在每行上面个插入1行
解答:1、在最左边插入一列,然后输入1、2、3、4、5、1、2、3、4、5,并以此列进行排序,在第一行上面再插入一行,删除刚刚插入的列。
2、使用Ctrl+鼠标一行一行选定,然后插入行。
3、sub 插入行()
for i=1 to 6
if cells(i,1).value <> Cells(i + 1, 1) And Cells(i, 1) <> "") Then
Rows(i + 1).Insert
end if
next i
end sub
可以检查一张表里是否有漏重的数字吗
答:漏值:{=IF(SUM((R1C1:R10C4="")*1)>0,"有漏值","无漏值")}
重复值:{=SUM(SUM((漏值!R1C1:R10C4=漏值!RC)*1))}
{=IF(MAX(R1C1:R10C4)>1,"有重复值","无重复值")}
使用下面公式更方便:
找重复值-------{=IF(SUM((COUNTIF(R1C1:R10C4,R1C1:R10C4)>1)*1)>1,"有重复值","无重复值")}
找 漏 值-------{=IF(SUM((R1C1:R10C4="")*1)>0,"有漏值","无漏值")}
注意:这两个公式均为数组,输入时应同时按Ctrl+Shift+Enter。
怎样将单元格中的公式转换为数值
解答:选中公式的一部分,按F9键
条件求和
有这样一个表格
A B C
1 2 3 /2 2 3 /3 2 3 /4 2 3 /5 2 3/6 2 3 /如何才能求出满足A列中大于2且小于5的B列和C列数值的和,要求B列和C列的值相加。用sumif函数似乎条件中只能设定为>2,而不能同时设定<5,而且在求和时只能B列相加,不能把B列和C列满足条件的值加起来。这个问题能不能只用函数,不用数组公式解决。请各位指教。
解答:1用公式:=SUM(IF(($A$1:$A$6>2)*($A$1:$A$6<5),B1:C6))
2用数组公式:{=SUM(IF($A$2:$A$7>2,IF($A$2:$A$7<5,$B$2:$B$7,0),0))+SUM(IF($A$2:$A$7>2,IF($A$2:$A$7<5,$C$2:$C$7,0),0))}
A1单元格为出生日期,可用=DATEDIF(A1,NOW(),"y")计算其年龄
这个公式是什么意思?K7=if(AND(R7>3000, Q7>0.5), "", P7)
意思是:如果R7单元格中的数值大于3000,并且Q7单元格中的数值大于0.5,则在K7单元格中显示空白,否则显示出P7单元格中的数据。
统计数据问题一例
各位朋友,如果我想统计50个数据中大于某个值的数据个数,(这个值是在使用时才输入某个单元格的),请问用什么函数,如何实现,谢谢。
如数据单元格为A1:E10,值的单元格为A11。
答:1、使用下面的数组公式: {=SUM(IF($A$1:$E$10>$A$11,1))}
2、输入以下函数: =COUNTIF(A1:E10,">"&A11)
关于条件求和问题!
有A,B,C,三列数据,如果A列符合要求,求B1*C1+B2*C2+......?
答:使用数组公式: {=SUM((R2C1:R13C1="ab")*(R2C2:R13C2)*(R2C3:R13C3))}
请教关于条件乖积的求和问题
A列为部门名称,B列为姓名,C列为日工资额(如20.00),D列为月出勤天数,我想在另一汇总表中汇总出各部门员工月工资总额(即:相应部门对应的C*D之和)。请问如何解决?
解答:1、=SUM((A4:A10="甲部门")*(C4:C13)*(D4:D13))
假设你的a列存放部门名称,你的b列存放员工姓名,C列存放日工资,D列存放天数。
计算“甲部门”的工资总额。
注意,这是数组公式,输入完毕后按ctrl+shift+回车
问:我试着把区域引用改为整列,出现错误,请指点!
=SUM((date!A:A="甲部门")*(date!E:E)*(date!F:F))
答:经试验,不能用整列方式,
你可以适当的调整一个比较大的区域如a2:a100 a1为标题行
因为如果参与计算的e列和f列区域出现文本,也会发生错误。一定要把计算区域的标题行去掉,并且保证不再数值区域出现文本。
=SUM((date!A2:A100="甲部门")*(date!C2:C100)*(date!D2:D100))
这个公式没有错误
文件修复
Excel文件是一个工作簿,一般可以包含255个工作表,每个工作表中可以包含大量的数据。如果一个Excel文件部分受损,不能正常打开,该怎么办呢?这里向大家介绍两种解救方法。
手动处理
进入Word,打开要修复的XLS文件,如果Excel只有一个工作表,会自动以表形式装入Word,若文件是由多个工作表组成,每次只能打开一个工作表。打开后,先将文件中损坏的数据删除。
用鼠标选中[表格]→[转换]→[表格转文本],注意可用“,”间隔符或其它分隔符,另存为一个TXT文本文件。在Excel中直接打开该文本文件,在打开时,Excel会提示文本导入向导,一般情况下只要直接点击[下一步]即可,打开后另存为其它的Excel文件即可。
注意:这种修复的方法是利用Word的直接读取Excel文件的功能实现,该方法在文件头没有损坏,只是文件内容有损坏的情况下比较有效;对文件头已经损坏的Excel文件,此方法可能不成功,必须借助于其它方法。
用Excel修复工具
Concept Data公司提供了一个专门用于修复Excel损坏文件的修复工具——ExcelRecovery,能够对Excel 5.0/97/2000的文件进行恢复处理,大家可以先
下载该软件的免费演示版试用,仅有674KB,
下载地址
http://download.sina.com.cn。软件安装后,可以自动将Excel的修复程序加在Excel应用程序中,在“文件”菜单下多出一项“Recovery”选项,若有损坏的文件,可以用该选项进行修复。另一个下载地
http://www.skycn.com/soft/7432.html 使用方法是:
1、打开Excel;
2、单击“文件”菜单下的“Recover”选项,Excel Recovery对话框将打开
3、指定要修复的Excel文件,然后按[Recover]按钮;
4、自动修复;
5、另存为一个新的文件名,即可完成文件的修复工作。
注意:该演示版本只能修复普通文件,不能修复带Visual Basic代码、图表以及包含口令的Excel文件,如果需要修复这些文件,请进行
注册。
另外要提醒大家的是:保存在软盘中的文件极易损坏,受损后可用以上方法修复,但如果损坏的文件位于磁盘0磁道时,就必须先修复软盘,再用上述方法修复Excel文件。
另外,考虑到未注册版本的使用限制,我找到如下破解页面,你自己再研究研究
Results 4 of about ExcelRecovery
URL:
http://www.cracks.am/cracks/e4.html ExcelRecovery v3.0
ExcelRecovery v2.2
URL:
http://www.thecrack.net:8080/db/list.php... 2064. ExcelRecovery 2.2 12 Kb
2065. ExcelRecovery 2.2.1 12 Kb
2066. ExcelRecovery v3.0 (SirCrack) 12 Kb
URL:
http://crck.passwd.ws/e.htm ExcelRecovery v3.0 - 11 Kb
URL:
http://cracks.allseek.info/crc/?p=E5 1255. ExcelRecovery 3.0 by SirCrack [ 11 Kb ]
显示隐藏的工作表
有个朋友给了我一个EXCEL程序,里面只有2个SHEET,但是我见到其中一个SHEET还引用了本文件另外一个工作表的内容,我用ALT+F11打开VB编辑器又可以看到那个隐藏了的工作表,
请问怎么能看到那工作表呢?
答:Sheets("name_of_sheet").Visible = True
这样一列如何筛选出含201的
县一高2014555 /便民201号 /县城301号/201414441/301745555/20145122 /柏良201 /柏良301
答:假设你的资料在B列,且起始行为第四行,即B4起始单元格,终止与11行处,即B11,则在A列输入公式IF(ISERROR(FIND("201",B4)),A3,A3+1),然后在D列输入顺序数字,比如1到10,在E列输入公式vlookup(d4,$a$4:$b$11,2,1>2)。注意A3为0。或:自动筛选->单击下拉列表->custom(自定义)->在条件中选"包含",在右边的文字框输入"201"->OK
两个日期相差的月份数
使用Datedif(日期1,日期2,"m")函数。
用函数实现连续相加
我有一公式是这样的:=sum(B1+B2+B5+B8+B11+B14+B17+B20)
也就是前两个是连续相加,后边的是每隔3个相加,不知有没有简单的公式。
答:{=SUM((MOD(ROW(B1:B20),3)=2)*B1:B20)+B1}
把计算结果为负值的显示为红色取整并在数字后面加上“仟元“
把单元格格式改为#,##0"仟元";[红色]-#,##0"仟元"
如果不要负号可以写成#,##0"仟元";[红色]#,##0"仟元"
比较A、B两列数据并在A列中包含B列的数据删除
工作表中A列数据是原始数据,而B列是另一些数据,现在要比较两列的情况,然后将A列中包含B列的数据删除
Sub wswx0041()
Dim i&, j&
On Error Resume Next
For j = Range("B65536").End(xlUp).Row To 1 Step -1
For i = Range("A65536").End(xlUp).Row To 1 Step -1
If Cells(i, 1) Like "*" & Cells(j, 2) & "*" And Not IsEmpty(Cells(j, 2)) Then Cells(i, 1).Delete shift:=xlUp
Next i
Next j
End Sub
怎样让我的图表随着数据透视表的更新
我的问题是: 1、当有新月份的数据出现的时候,图表无法包含新数据;2、但REGION选择不是全部,比如CD,图表中没有数据。怎样实现图表随数据透视表的更新而更新呢?
解答:DATA=Data!$A$1:$G$129、固定的范围,不值钱、DATA=OFFSET(Data!$A$1,,,COUNTA(Data!$A:$A),6)
动态范围、按数据透视表[!]按钮,更新数据
Sales Date;改用年月日三个字段;目的是当[索引],让条件容易下;DATA=OFFSET(Data!$A$1,,,COUNTA(Data!$A:$A),8)。
直接由[数据透视表]工具,[图表精灵]出,[图表]
如此就是完美的三层式结构,分工完成工作,只不过是接条龙,很简单吧!
又问:每次刷新数据,列宽等格式就需要重调,有没有办法将格式固定住?
答:工具>>选项>>一般>>标准字型>>大小>>确定。设定好以后,开新档案,列宽行高自动会调整。但这不是重点
[数据透视表];鼠标右键>>分页显示。此功能是用来打印整本活页簿,出报表用
双击总计字段下之单元格;此功能是用来列出该笔合并数据所有明细;所以[自动筛选][进阶筛选]就用不着了,也不必去写复杂之[数组公式];需要甚么数据,直接找[数据透视表]要;操作非常简单
DATA可以转到mbd檔;ACCESS可以汇入Excel数据,用精灵操作;汇入以后xls档即可删除
Excel>>数据>>取得外部数据>>新增外部数据查询>>>>>>转入[数据透视表];此物即为Microsoft Query
第一次使用会要求放入光盘片,安装ODBC驱动程序
改用年月日三个字段;是有道理的。别嫌麻烦
循环引用问题
vba中输入公式,我想用变量代替RC地址,以便循环操作,如何做到?比如:在Range("am6").FormulaR1C1 = "=SUM(R[-5]C:R[-5]C[10])"中,如何才能用变量x来代替其中的10或-5等数字?
解答:="=SUM(R[" & x & "]C[" & y & "]:R[" & xx & "]C[" & yy & "])"
如何才能有条件的引用某一区的内容
请教各位,怎样才能引用符合条件的某一区域的内容。如下:
A B C
9-1 1 9-15 /9-2 2 9-16 /9-2 2 9-17 /9-2 1 9-18 /9-4 2 9-18
请问,用什么方法才能在另外的单元格中引用同时符合A列中“9-2”和B列中“2”的C列的内容。
解答:先判断个数,再列出符合的数据:
{=IF(ROW()-ROW($A$18)+1>SUM(($A$2:$A$7=$B$16)*($B$2:$B$7=$C$16)),"",TEXT(SMALL(IF(IF($A$2:$A$7=$B$16,$B$2:$B$7,"")=$C$16,$C$2:$C$7,""),ROW($A1)),"m月d日"))}
Excel基本功
1.引用同一工作薄中另外一工作表的单元格
= Worksheets("工作表名称").Cells(1, 1)
------------------------------------------------------------
如当前工作表为sheet1,想引用sheet2中的B2,则=sheet2!B2
2.如何使0值不显示
方法1:if (A1<>"", A., "")
方法2:对于整个工作表中的0值全不显示,[工具]-[选项]-[视图],清除0值选项。
方法3:用自定义数字格式,其中"#"和"?"有屏蔽0值的效果。比如:"G/通用格式;G/通用格式;#","G/通用格式;G/通用格式;?"。
3.如何定义格式和Copy格式?
在EXCEL中,可不可以把某一范围定义成一种格式,而其中某些数据又不是.并且又如把这种格式COPY到同一张sheet1中?如:
大制程 标准工时 状况 2002/1/24 2002/1/25
二次 13 /焊接 20 .而其它空格就不包括在格式中.被填写了的这张表格的格式又能重新COPY.如何做呢?
解答:新建一个只有一个工作表的工作簿 ,在这个工作表中设计你的格式另存为模板(*.xlt)。以后插入新工作表时,选择刚才建的模板。
又问:但是假如我只有一张工作表,我如何在同一张工作表中COPY呢?如我的固定格式在sheet1的A1:Z15范围内,我要把这种格式copy到A17:Z24,而其中的数据又不COPY进来.怎么办?
解答:假设你要复制格式的范围为A1:Z1和A1:B15两个区域,则用下面的宏能实现你的复制要求。
你只要选定新区域左上角的单元格,然后执行宏即可。
Sub 宏1()
aaa = ActiveWindow.RangeSelection.Address
Range("A1:Z1").Select
Selection.Copy
Range(aaa).Select
ActiveSheet.Paste
Range("A1:B15").Select
Application.CutCopyMode = False
Selection.Copy
Range(aaa).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(aaa).Select
End Sub
------------------------------------------------------------
如果A1:Z15中的数据全部都要清除,
可以[复制]A1:Z15,[选择性粘贴]到A17,粘贴栏中选中"格式"。
------------------------------------------------------------
选定范围后直接使用格式刷就可以了
4.如何实行列互换?
解答:Transpose函数
或选中一行或一列,复制,选择性粘贴->转置。
------------------------------------------------------------
在某一单元格输入公式“INDEX(reference,COLUMN(A1),ROW(A1))”
然后向下向右拖放。
5.EXCEL2000中视面管理器如何具体运用呀?
请问高手EXCEL2000中视面管理器如何具体运用呀?
------------------------------------------------------------
其实很简单呀,你把它想象成运动场上的一串照片(记录不同时点的场景),
一张照片记录一个场景,选择一张照片就把运动“拖”到照片上的时点。
不同的是只是场景回复,而值和格式不回复。
解除officeXP 50 次限制
1、 首先使用下面这个序号来安装Office XP。
BMV8D-G272X-MHMXW-4DY9G-M8YTQ
2、安装后,启动新建一个Word文档,程序会提示你激活,不管它!关闭Word。
3、打开注册表编辑器REGEDIT.exe,进入到下面的子键里面:
[HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Windows/CurrentVersion/Installer/Products/4080820900063D11C8EF00054038389C/Always Installed]
4、把右边的这个DWORD类型的键删除:"Usage"=dword:2adb0001
5、在右边新建这样一个字符串型的键:Usage,将其值改为AlwaysInstalled 即: Usage="AlwaysInstalled"
6、最后使用
法国人写的字节数为12,800的Crack.exe来破解Office XP后,即大功告成!
我破解后,打开Word 70次,打开Excel 60次后,还是能够新建文档、表格,而且可以顺利保存。
无论Win98或Win2000操作系统我都成功了,且已运行了200次以上!
小写数字转换成人民币大写