Excel word ppt office使用技巧大全(DOC格式)-第70部分
按键盘上方向键 ← 或 → 可快速上下翻页,按键盘上的 Enter 键可回到本书目录页,按键盘上方向键 ↑ 可回到本页顶部!
————未阅读完?加入书签已便下次继续阅读!
一份工作表,有几万行数据,现在想每隔 50 行打印一行,如何进行筛选
解答:插入一空列;输入=MOD(ROW();50)并复制到全数据列;再用筛选法把有“0 ”的单元格选
出。
Word 加启动密码
在 Word 模板中录制如下宏文件保存即可:
Sub autoexec()
376
…………………………………………………………Page 377……………………………………………………………
Dim user
user = InputBox(〃请输入软件启动密码〃)
If user = 〃password〃 Then
Else
MsgBox (〃非法用户;将退出本程序。Wu〃)
Application。Quit
End If
End Sub
有关大小写的转换
在 EXCEL 单元格中有的单元格是小写字母,有的是大小写混在一起现在我想全部转为大写、
或全部转为小写,有哪些方法呢?
利用 upper() 函数或还有 LOWER 。如:a1=aBcD,upper(a1)=ABCD
在 Excel 中打造自己的“公式保护”菜单
工作中经常用到 Excel 的计算功能,有时一张工作表中需要设置很多公式,为了防止误操作就
将工作表保护起来,但是如果将含有公式的单元格保护,而其它单元格不锁定,设置时需要一
个个单元格选定,很麻烦而且容易出错,就想到建立一个菜单项,将这项工作变成菜单 。有
幸的是这一切 Excel 都能完成,具体操作如下:
先将工作表保护,不必输入密码。
再录制一个宏。方法是:打开“工具”栏,单击“宏”“录制新宏”;将其命名为“公式保
护”,单击“保存在”的下拉箭头,将新宏保存到“个人宏工作簿”。单击“确定”录制开始。
(注意此后的每一动作都将被录制,直至“停止录制”。)
1。 将工作表取消保护;
2。 选定 a1 单元格,输入=〃测试〃 (注:黑体为实际输入内容,标点符号均为英文输入法
状态;下同);
3。 单击工作表左上角的方框选定整个工作表;
4。 单击“格式”“单元格”“保护”项,将“锁定”和“隐藏”前的方框里的对号去
掉;
5。 单击“插入”“名称”“定义”,当前工作表名称输入:公式保护,引用位置输入:
=get。cell(4;indirect(〃rc〃;false)); 单击“添加”后“确定”;
6。 单击“格式”“条件格式”,在“单元格数值”下拉框选定“公式”,后面的框内
输入=公式保护 ,单击“格式〃”,在图案标签内选浅绿色或者其它颜色“确定”,再“确
定”;
7。 单击 “编辑”“定位”“定位条件〃”,选定“公式”项“确定”再“确定”;
8。 重复步骤 4,将“锁定”和“隐藏”前的方框里的对号选定;
9。 选定 a1 单元格,取消其内容,并重复步骤 4,取消 a1 的锁定和隐藏属性;
10。 单击工具…保护…保护工作表,将工作表保护(也不必设密码)。再单击“工具”“宏”
“停止录制”停止录制。再打开“工具”“自定义”项,再“命令”标签栏内“类别”
栏中找到“新菜单”并指定,在其右侧的命令框中的新菜单用鼠标按住拖到“工具栏”的
“保护”项中的“保护工作表”下面放开,并单击右键将其命名为“公式保护”,将刚才
录制名为“公式保护“的宏指定给它。至此,一个很有用的菜单项就作成了。 此后,只要
你将鼠标移动到“工具”“保护”“公式保护”的位置,工作表将执行其所指定的宏,
377
…………………………………………………………Page 378……………………………………………………………
只要你工作表中输入了公式(以=开头),含有公式的单元格将自动变为浅绿色,提醒你和
别人此处有公式,小心编辑,十分醒目。编辑完公式后再次运行该命令就可以此保护工作
表并锁定公式,禁止改动。
如何让 EXCEL 自动从头统计到当前单元格
情况如下: C 列要根据 A 列的内容来统计 B 列的数据,范围从 A1 :An ,即当A 列中 An 有
数据时,Cn 自动根据An 的值,统计B1 :Bn 的数据。
解答:=SUM(INDIRECT(〃B1:B〃 & LARGE((A1:A65535〃〃)*(ROW(A1:A65535));1))) ,按 Ct
rl+Shift+Enter 。
请问想要取当前单元格的列号,用什么函数
1、=CHAR(64+COLUMN())
2 、Private Sub Worksheet_SelectionChange(ByVal Target As Excel。Range)
If Selection。Columns。Column 》 26 Then
tt = Mid(ActiveCell。Address; 2; 2)
Else
tt = Mid(ActiveCell。Address; 2; 1)
End If
MsgBox (tt)
End Sub
3、1 的公式就变化一下:=IF(COLUMN()》26;CHAR(64+INT(COLUMN()/26)) & CHAR(64+
MOD(COLUMN();26));CHAR(64+COLUMN()))
SUMIF 函数
a1 至 A4 是 10;24;30;12。B1 至 B4 是 8;15;25;35。D1 至 D4 是 25;4;3;5。用 SUMIF 第一参数选取 A
1:B4;第二参数是》20;第三参数选取 D1:D4。它得出是 7 。它判断的是A1:A4 。我的要求是 B1:
B4 也跟着判断 ; 也就要得出来得是 12
解答:{=SUM((A1:A4》20)*(B1:B4》20)*C1:C4)}
378
…………………………………………………………Page 379……………………………………………………………
怎么能快速的将两个单元格的内容互换
1、
Sub ChangVal()
my1value = ActiveCell。Value
For Each a In Selection
If a。Address ActiveCell。Address Then
my2value = a。Value
a。Value = my1value
ActiveCell。Value = my2value
End If
Next a
End Sub
2 、用鼠标先选定单元格,点住单元格边框,并按住 SHIFT 键,然后托拽到隔壁单元格的后面
一条边框处。你能看到被托拽部分会变成灰色的“工”字形,然后放手后,单元格就互换了。
如何能到两个时间段的 17:00…8:00 小时数?
1。已知〃E4〃为 08011500(为文本格式﹐开始时间﹐意思为 8 月 1 日 下午 15 ﹕00) ﹐〃G4〃为
08100900(为文本格式﹐结束时间﹐意思为 8 月 10 日 上午 9 点) 问﹕如何能得到开始时间到
结束时间(17:00…08:00 )的总小时数?
2。 如何得到开始时间到结束时间的节假日时数?(如 5。1 ﹐10。1 ﹐星期六﹐星期天)
解答:方法 1:
=(DATE(YEAR(NOW());MIDB(AF4;1;2);MIDB(AF4;3;2))…DATE(YEAR(NOW());MIDB(E4;1;2);MI
DB(E4;3;2))…1)*15+IF(MIDB(E4;5;2)+MIDB(E4;7;2)/60=17;24(MIDB(E4;5;2)+MIDB(E4;7;2)/60);7))
+IF(MIDB(AF4;5;2)+MIDB(AF4;7;2)/60=17;MIDB(AF4;5;2)+MIDB(AF4;7;2)/60…9;8))
方法 2 :networkdays 需要你提供一个节假日列表作为参数。我猜你不会喜欢手工输入这样一
个表。以下公式功能更为强劲,它可以算出两个单元格(A2 和 C2)所储存的日期之间的周末
天数,乘以小时数即可计算出你所说的节假日时数。但是,它无法计算国庆节等公众假期。
=IF(C2》A2;SUM(IF(WEEKDAY(C2…ROW(INDIRECT(〃1:〃&
C2…A2));2)》5;1;0));SUM(IF(WEEKDAY(A2…ROW(INDIRECT(〃1:〃& A2…C2));2)》5;1;0)))。这是
一个数组公式,输入完成后按 CTRL+SHIFT+ENTER 结束。
方法 3 :用格式定义 E4;G4 为日期时间格式:****…**…** **:**。然后:(G4…E4)*24 即可得两
时间内的小时数。
如何在单元格返回工作表名称
答:=RIGHT(CELL(〃filename〃);LEN(CELL(〃filename〃))…FIND(〃'〃;CELL(〃filename〃)))
如何在输入数字的加减乘除按 ENTE 后能在另一单元格自动出现计算数值
1、编了个宏:
Sub aa()
Cells(1; 1) = Mid(ActiveCell。Formula; 2; 13)
379
…………………………………………………………Page 380……………………………………………………………
End Sub
先在单元格里输入公式得出结果;选定得出结果的单元格运行宏就可以在A1 出文本。
2 、宏(测试通过)
Sub aa()
A = 2 '行'
B = 4 '列
LINE1:
If Cells(A; B) = 〃〃 Then
Exit Sub
Else
Cells(A; B + 1)。Formula = 〃=〃 & Cells(A; B)
A = A + 1
GoTo LINE1:
End If
End Sub
有 A1,B1,C1,D1 四个单元格D1 的值要随着A1的变化而变化
A1有三种变化,一、二、三;当A1=‘一’时,D1=“ ”; A1=‘二’时,D1
=(B1+C1)/3 ; A1=‘二’时,D1=(B1+C1)/6 。以上可以通过I
F来完成 。可我希望的是:当B1或C1为空时,不论A1为什么D1都为空,这样做得到
么?
解 答 : 1 、 =IF(OR(B1=〃〃;C1=〃〃);〃〃;IF(A1=〃 一 〃;〃〃;IF(A1=〃 二 〃;(B1+C1)/3;IF(A1=〃 三
〃;(B1+C1)/6;〃〃))))
2 、=IF(OR(B1=〃〃; C1=〃〃; A1=〃一〃); 〃〃; IF(A1 = 〃二〃; (B1+C1)/3; (B1+C1)/6))
18、 如果考虑 A1 没有数据的话:=IF(OR(B1=〃〃; C1=〃〃; A1=〃一〃;A1=〃〃); 〃〃; IF(A1 = 〃二〃;
(B1+C1)/3; (B1+C1)/6))
对 A 列不重复的数值计数
我只能做到新建一列,B 列,然后第一个单元格 countif (A1:A100;A1),然后拖动到全部
新列。最后在新列下面用 sumif(B1:B100;1) 谁有更好地方法。
解答:1、{=SUM(IF(A1:A100=〃〃;〃〃;1/(COUNTIF(A1:A100;A1:A100))))}(又
问:公式中的“1/(COUNTIF(A1:A100;A1:A100))”像是一个倒数,怎么理解?答:
用倒数是这个意思:如果只出现一次,数组中的相应项统计为 1,其倒数为 1,Sum 统计计 1。
如果出现 N 次,其倒数为 1/N,出现了N 次,求和就是 Nx1/N,最后 Sum 统计就只计 1。)
永恒的求和
1、=SUM(OFFSET(A1;;;ROW()…ROW(A1)))可以对 A 列数值自动求和。
2、=SUM(INDIRECT(〃R2C:R'…1'C〃;FALSE))
3、=SUM(INDIRECT(〃A2:A〃&ROW()…1))
380
…………………………………………………………Page 381……………………………………………………………
19、 坚持不用 R1C1 栏名列号表示法还有一个方法,不过又复杂了些。首先定义一个公式:
COL=IF(COLUMN()》26;CHAR(INT((COLUMN()…1)/26)+64)&CHAR(IF(MOD(COLUMN();
26)=0;26;MOD(COLUMN();26))+64);CHAR(COLUMN()+64)) 。后于欲求加总之储存格输入:
=SUM(INDIRECT(COL&〃1:〃&COL&ROW()…1))。则此公式复制到任何一任皆可用,又不怕
产生错误值。(注:COL=IF(COLUMN()》26;CHAR(MOD(COLUMN();26)+64)&CHAR(INT(C
OLUMN()/26)+64);CHAR(COLUMN()+64)) 。暴露了一个为人不知的缺点,如果列数到了A
A 列以后就不行了,虽然可用 ADDRESS()解决,比用 CHAR()好多了,但公式还是太长,
用在一个加法中实在不值)
20 、 其实用 ADDRESS 更好,=SUM(INDIRECT(ADDRESS(1;COLUMN())&〃:〃&ADDRES
S(ROW()…1;COLUMN()))),还是一句老话,为做一个加法不是太值,这只能是技术上的讨
论。
如何使用 EXCEL 进行动态跨表取数
有两