|
|
发表于 2009-12-17 08:49:58
|
显示全部楼层
实用技巧] EXCEL小技巧
1、两列数据查找相同值对应的位置bbs.canet.com.cn* S- b0 y6 |& Z& z( T3 ?
=MATCH(B1,A:A，0)
. N5 M3 M! i7 f# b9 X2、已知公式得结果中国会计社区,会计,注册会计师,会计论坛,会计社区, l* F% D! |/ I- A
定义名称=EVALUATE(Sheet1!C1)
7 p1 b# v' b X: c中国会计社区已知结果得公式/ S, ~( @! }: V/ O9 o
定义名称=GET.CELL(6,Sheet1!C1)
- N$ }' T& k2 x0 ~3 u中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办3、强制换行中国会计社区,会计,注册会计师,会计论坛,会计社区; T) R T% s# ^ J5 C+ U8 I
用Alt+Enter' ?0 h3 M* t3 }+ C5 l( {! ?
4、超过15位数字输入7 v7 P3 S9 `# a: X+ h3 u7 l/ F
这个问题问的人太多了，也收起来吧。一、单元格设置为文本；二、在输入数字前先输入'
0 K4 p* v3 d% {* |5、
1 q* L. ?) U4 m! ] vbbs.canet.com.cn在EXCEL2000中，我们可以设置工作簿的共享来加快数据的录入速度，而且在工作过程中还可以随时查看各自所做的改动。当多人一起在共享工作簿上工作时，Excel会自动保持信息不断更新。在一个共享工作簿中，各个用户可以输入数据、插入行和列以及更改公式等，甚至还可以筛选出自己关心的数据，保留自己的视窗。+ h2 B+ w3 E! ?) i3 z0 L0 ?3 Y
一、设置共享工作簿/ e- g- M" ?+ t
我们可以先举个例子：假设有甲乙丙丁四个用户分工合作共同完成录入操作，四人分别在001、002、003、004号机器上同时操作，这四台机器都已经与000号电脑通过网络连接了起来，学生学籍资料信息2002.xls存放在000号机器的硬盘E:\上。
! Y0 K# n4 V* }5 z& [4 a中国会计社区,会计,注册会计师,会计论坛,会计社区 1、在“工具”菜单中单击“共享工作簿”命令，然后单击“编辑” 标签，如图所示。
: W4 K: ]7 u$ d中国会计社区 2、选择“允许多用户同时编辑，同时允许工作簿合并”复选框，在“高级”标签中可以对“自动更新间隔”、“用户间修订冲突解决方法”以及“个人视图属性”等进行设置，在通常情况下，“高级”标签中的各个参数可直接使用系统默认值，然后单击[确定]即可。! _) D4 h9 g3 s
3、出现提示时，保存工作簿。
- i% V( s' `' ?1 w$ C中国会计社区 4、在“文件”菜单中单击“另存为”，然后将共享工作簿保存在其他用户可以访问到的一个网络资源上（如000号机器的E:\）。
* }, k# R: q7 I* i7 k$ {; b中国会计社区,会计,注册会计师,会计论坛,会计社区 二、使用共享工作簿
* N; g8 [* ^' l' A7 J 1、查看冲突日志
* d0 M+ F& _$ ?2 U4 F9 b中国会计社区 冲突日志在共享工作簿中，是为以前编辑时所作的更改保存的信息，信息包括每个更改的作者、进行更改的时间和被更改的数据内容等。
6 h/ _) t# W% I* U* ` 查看冲突日志有两种方法：一是在工作表上将鼠标箭头停留在被修订过的单元格上，突出显示详细的修改信息；二是在单独的冲突日志工作表上罗列出每一处冲突，方法如下：& l0 p' j V1 ^: `" O3 x
（1）在“工具”菜单中，选择“修订”子菜单，再单击“突出显示修订”命令。
( Y7 H# L6 |% h; J中国会计社区 （2）选中“编辑时跟踪修订信息，同时共享工作簿”复选框，该复选框将开启工作簿共享和冲突日志。中国会计社区; \+ ^: O: J! t5 D s
（3）选中“在屏幕上突出显示修订”复选框，这样在工作表上进行需要的修改后，Excel会以突出显示的颜色标记修改、插入或删除的单元格。
& x; S# v N8 o) y9 j* @1 _$ d （4）选中“在新工作表中显示修订”选项，将启动冲突日志工作表。bbs.canet.com.cn, s- O7 C1 @$ ^! D' y
（5）单击[确定]按钮，当弹出对话框提示保存工作簿时，请再次单击[确定]按钮，保存工作簿。中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办9 Y& o$ W5 }* Y4 w" g
二、合并工作簿
3 R2 G: J& [( Z中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办 当合并用户需要获得各自更改的共享工作簿的若干备份时，Excel需要共享工作簿的每个备份都保留有从创建备份之日到合并之日的所有冲突日志，同时如果天数超过指定的保留时间，就不能再合并备份。保险的办法是将冲突日志保留足够长的时间，或是输入一个极大的天数，例如1000天。; N. c ] w( B4 K
合并工作簿具体操作如下：
/ S a) L6 g1 [. h. j1 t+ U& @7 ? （1）在欲合并的另一个工作簿文件之中，打开共享工作簿的备份。
& v! P$ n0 Z7 D3 g5 Z r6 j, l. p. B中国会计社区 （2）单击“工具”菜单中的“合并工作簿”命令。
! g( p' V7 z; d5 wbbs.canet.com.cn （3）如果有提示，请保存共享工作簿。
N% p# r* v3 Z7 s8 n中国会计社区 （4）在“将选定文件合并到当前工作簿”对话框中，单击包含有合并更改内容的共享工作簿的某一个备份，然后单击[确定]按钮。5 _( J, c5 W8 _. `" L
（5）重复第（2）步到第（4）步，直到共享工作簿的所有备份都被合并。
X% s# g$ O& o) o# L0 t8 o$ j! ]中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办 做好以上设置后，其他用户都可以在自己的电脑中同时编辑、浏览约定的工作簿，同时000号操作员可以在自己的窗口中看到并管理所有用户共享编辑这个工作簿。为方便实际操作，应将每位用户姓名更改为真名实姓，方法是：单击“工具→选项→常规→用户姓名→输入姓名→确定”，这样在打开“共享工作簿”弹出的对话框（如前图所示）中便可正确显示正在使用本工作薄的所有用户姓名和开始使用时间。为了避免几个用户修改同一单元格的内容，可以估计一下数据中的记录数量，让几位用户每人分配不同的录入部分。比如让甲从工作薄的第1条开始录入，让乙从工作薄的第200行开始……但应注意在“共享工作簿”状态下不能同时编辑批注和建立超链接，这两项只能在不共享状态下进行。$ ]: O6 f R- M/ b% C: f9 C' w& U
?提示?: E s0 ~9 g; [* I9 T4 I* _
1、如果需要取消共享工作簿，只需要在图1的“编辑”标签中，清除“允许多用户同时编辑，同时允许工作簿合并”项即可，不过这将使其他用户丢失所有未保存的修改。中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办) b/ d" l! h; S
2、对工作簿进行共享设置后，可以由网络上的多位用户同时访问和修改，为避免丢失修订记录，可以为工作簿指定一个密码来保护共享，或保护其他工作表及工作簿特性，可作如下操作：
: r, u" y$ @8 n/ q9 O% }6 D: m中国会计社区,会计,注册会计师,会计论坛,会计社区 （1）单击“工具”菜单的“保护”命令，从其子菜单中选择“保护并共享工作簿”命令，弹出“保护共享工作簿”对话框（如图）。中国会计社区7 H' v0 ~* d0 Y4 o9 V0 K3 h
（2）选中“以追踪修订方式共享”复选框，用这种方式就可以共享工作簿且避免丢失修订记录。如果希望其他用户在关闭冲突日志或撤消工作簿共享状态时输入密码，请在“密码”框中键入密码，然后在出现提示时再次输入以便确认。为工作簿提供共享保护以后，其他用户就不能撤消工作簿共享状态或者关闭冲突日志了，除非知道正确的密码。
& X! \' B* X P; ]7 x6、EXCEL中行列互换
) y! t& Q$ x2 C5 M8 ~1 Y复制，选择性粘贴，选中转置，确定即可' E$ b( Y* _$ j
7、Excel 是怎么加密的
5 a, z+ N# I1 } r中国会计社区,会计,注册会计师,会计论坛,会计社区 1、保存时可以的另存为>>右上角的"工具">>常规>>设置( V4 t6 f# z. u1 ?7 C# X E
2、工具>>选项>>安全性
% o, @3 \& I" X3 E2 o中国会计社区,会计,注册会计师,会计论坛,会计社区8、关于COUNTIF
! f! j9 X5 d h- ?( NCOUNTIF函数只能有一个条件，如大于90，为=COUNTIF(A1:A10,">=90")
; h1 i W2 X: J5 \中国会计社区,会计,注册会计师,会计论坛,会计社区介于80与90之间需用减，为 =COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90")
+ c& I8 `( P9 c4 d9、根据身份证号提取出生日期
% Y1 e) c. ~5 z5 H 1、=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),"错误身份证号"))中国会计社区2 m$ l3 g5 W* i, Q" k, p: L/ N; X
2、
/ a% p, W9 A1 m, H=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1
/ b( o6 l4 J" n7 N* m7 F中国会计社区10、想在SHEET2中完全引用SHEET1输入的数据中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办/ T) Q5 q& b+ K5 h- J8 i
工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。 中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办8 c3 o) K6 P+ }
11、一列中不输入重复数字中国会计社区6 r1 I0 M# t' n7 r# f2 Z& b
[数据]--[有效性]--[自定义]--[公式]
: b7 v+ `% M$ S' j中国会计社区,会计,注册会计师,会计论坛,会计社区中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办9 O" N/ y d* |' H- g
输入=COUNTIF(A:A,A1)=1bbs.canet.com.cn( }% S) w$ Z$ y& L
如果要查找重复输入的数字
2 A0 L4 i+ o. C" Q条件格式》公式》=COUNTIF(A:A,A5)>1》格式选红色bbs.canet.com.cn& j. I# G! n6 c) L; E
12、直接打开一个电子表格文件的时候打不开" B0 E. w L/ x* t0 n1 p
“文件夹选项”－“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%,如果没有，请手工加上
3 |0 g; t- P$ S, k8 i0 l( A$ ~, Zbbs.canet.com.cn13、excel下拉菜单的实现: j9 R7 E. F: }; L6 q8 s
[数据]-[有效性]-[序列]中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办- C7 p7 ?0 U/ T1 U
14、 10列数据合计成一列5 h" K6 ]( H' J x; z1 N6 C4 J2 v
=SUM(OFFSET($A$1,(ROW()-2)*10+1,,10,1))
# k# m4 i5 w3 B/ k4 Zbbs.canet.com.cn15、查找数据公式两个(基本查找函数为VLOOKUP,MATCH)中国会计社区5 ~; n9 J3 L* e6 C+ q# M) q
1、根据符合行列两个条件查找对应结果
c9 n7 D! J$ P+ \" K/ p中国会计社区 =VLOOKUP（H1，A1：E7，MATCH（I1，A1：E1，0），FALSE）( i: G$ T1 v/ V5 P
2、根据符合两列数据查找对应结果(为数组公式)中国会计社区; z/ L. J- k3 m. O8 {& S9 @
=INDEX（C1：C7，MATCH（H1&I1,A1:A7&B1:B7,0））) W" [1 U! J8 Q* t+ K: z
16、如何隐藏单元格中的0; b; V" E( l5 w! l4 J
单元格格式自定义0;-0;;@ 或 选项》视图》零值去勾。呵呵，如果用公式就要看情况了。
6 c7 a* L; i& R* s, D3 E中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办17、多个工作表的单元格合并计算
# Q3 [0 C2 p, _, h=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4). K. V% L9 s7 O# ~
18、获得工作表名称
) f- R6 Q& U7 y& pbbs.canet.com.cn 1、定义名称：Name
1 y* H9 I: n9 a7 `" u' c! y& z0 \中国会计社区,会计,注册会计师,会计论坛,会计社区 =GET.DOCUMENT(88)
# j) w/ U8 ?) {中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办 2、定义名称：Path中国会计社区,会计,注册会计师,会计论坛,会计社区7 A4 T; C- a- k
=GET.DOCUMENT(2)中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办) R0 [- j. `; O
3、在A1中输入=CELL("filename")得到路径级文件名
; f1 w3 X1 F+ H) c# M8 y中国会计社区 在需要得到文件名的单元格输入
6 i' O) ^ Z9 P6 O4 }中国会计社区 =MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1)), y: y4 o( \ b c# E! Q! `& R0 ^
4、自定义函数
; X9 u `8 g& q( b% N Public Function name()
- T- Q3 k6 w, o中国会计社区,会计,注册会计师,会计论坛,会计社区 Dim filename As String
6 j$ u% y3 _( ^, x; \ filename = ActiveWorkbook.name
( m0 Z) l W8 r中国会计社区,会计,注册会计师,会计论坛,会计社区 name = filenamebbs.canet.com.cn3 a5 V' E9 D) J3 B1 E" E+ c1 y. I
End Function
0 H& z/ {$ W( C: ?0 W7 |, }) [bbs.canet.com.cn19、这个在一个帖子中的我就整搬一下（自已的帖^_^）中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办, N& Z* e% K Z2 r& u
如何获取一个月的最大天数+ d% H7 y& D9 B: h
:"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1为"2001-03-01
4 \1 x% b) p. w5 d5 b数据区包含某一字符的项的总和,该用什么公式中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办0 ?' E7 s# {. E! W
=sumif(a:a,"*"&"某一字符"&"*",数据区)
6 {+ Q% Y5 A% q; V T) h中国会计社区,会计,注册会计师,会计论坛,会计社区最后一行为文本：中国会计社区,会计,注册会计师,会计论坛,会计社区# n! {/ Y7 G- W2 ?9 C; R0 c- ~
=offset($b$1,MATCH(CHAR(65535),b:b)-1,)
$ ]! g. Y) a! jbbs.canet.com.cn最后一行为数字：
& ^ S; W% w) ^4 n) L, {3 \中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办=offset($b$1,MATCH(9.9999E+307,b:b)-1,)/ e# s8 F7 ?& g5 ~* g
或者：=lookup(2,1/(b1:b1000<>""),b1:b1000)
4 {: g( k7 }# p5 H# y9 p' |+ ?; Nbbs.canet.com.cn评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分？同时显示出被去掉的分数。
& I8 T. L0 j4 x, D看看trimmean()函数帮助。
: L4 l s0 {3 f! ]2 Q ^/ v6 Y% d中国会计社区被去掉的分数：8 N/ H! n/ d+ N( [2 P- Z
最大两个：=large(data,{1;2}): _2 Z3 H" H: o9 D/ U2 r+ K
最小两个：=small(data,{1;2})中国会计社区,会计,注册会计师,会计论坛,会计社区1 ~4 N$ a% P3 u) s! {0 B/ H( a
怎样很简单的判断最后一位是字母8 ^3 }- c" h- X
right(a1)*1* N* a% I$ X1 P% `# f" r5 D
出错的字母
! f4 E! y$ O( l1 X中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办=IF(ISNUMBER(--RIGHT(A1,1)),"数字","字母")中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办- l3 @, e) v1 ]
=IF(ISERR(RIGHT(A1)*1),"字母","数字")6 A; A" Q' r5 r) O- g: ^
如何 设置单元格，令其不接受包含空格的字符bbs.canet.com.cn3 w* V* e/ t8 ]5 O6 C6 X
选定A列
5 V4 B$ U$ `3 K数据——有效性——自定义——公式
$ I6 W, x! ^( d( T& |0 Y=iserror(find(" ",a1))3 [; ~' C1 d" @3 ?8 w
数据－－有效性－－自定义－－公式7 k! A0 |) U* |% ], w* Z% T
=len(a1)=len(trim(a1))bbs.canet.com.cn5 i: _" Q7 g8 P2 r5 [: b4 b
原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22.....
( w4 t! X' _( E4 M! m& [ b中国会计社区,会计,注册会计师,会计论坛,会计社区现在是=sum(n(offset(a1,(row(1:10)-1)*3,)))
* ] N4 g% ?( l4 i7 Bbbs.canet.com.cn在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据我输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用?中国会计社区,会计,注册会计师,会计论坛,会计社区2 N3 {, q4 ]8 e+ i$ J4 A# D/ l E
=INDIRECT("A1"&"!"&"E1") A1为工作表名
8 }. T" V$ t7 w! E+ b9 q% j4 z中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办奇数行求和 =SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2))中国会计社区' w5 W# k, j+ @+ G' W3 B3 J4 k/ I, I: z* l
偶数行求和 =SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2)))
2 q) C3 ^( b* H" M' _: [/ _! s' w B中国会计社区查看字符串字数
8 l- Q/ U" }( m2 W) P- f* Q! H中国会计社区 =LEN(A1)
, ^! Z5 G1 m& R9 a$ G0 D+ s/ D m3 o中国会计社区,会计,注册会计师,会计论坛,会计社区求非空单元格数量
" ]6 n5 t9 K; A7 ~) h8 t公式计算出来的数据，COUNTA不能用的（否则空字符也计算进去了）* p# }, G9 g8 V% c+ M! W% S
=COUNTIF($E$3E$65536,"?*")% N! B+ M2 J4 T2 r. d& B/ z7 h6 j
动态求和公式,自A列A1单元格到当前行前面一行的单元格求和.
# Z, ]* q, i" W) g=SUM(INDIRECT("A1:A"&ROW()-1))
) x$ u; r* E; ~" H+ S4 B, f20、比较好用的EXCEL文档修复工具
/ l* Z6 p5 D4 D9 V* n中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办ExcelRecovery* O- d. x) g/ M& ?
中国会计社区,会计,注册会计师,会计论坛,会计社区) H: J. A& V& @0 J# D' a
21、EXCEL开方运算：将8开3次方，可以用这个公式，在单元格中输入 中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办0 ?# ~* n) P+ Z$ [1 H% k5 @
=8^(1/3) 中国会计社区,会计,注册会计师,会计论坛,会计社区4 ? A% A6 D H
22、单元格中的数据分散对齐
1 M; p) F+ V7 c5 F* G+ R$ m中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办文本格式》全角输入
6 e- a" S& [0 |$ C# e中国会计社区,会计,注册会计师,会计论坛,会计社区23、查找工作表中的链接) S* k, W) `0 q: F' N6 l, g
Ctrl+~ 或编辑》链接中国会计社区* y% p) N" }* C/ v( y9 n
24、如何让空单元格自动填为0中国会计社区$ S6 E: L! Y0 R* C) Q; p/ {
选中需更改的区域》查找》空》替换》0
/ `7 l+ k1 J- e" g% @+ Y( _bbs.canet.com.cn25、把Word里的数字转换到Excel中国会计社区+ U/ V! [. N0 e) e/ }/ g* I- q: N4 m" u
方法有多种，选中》复制》设置输入单元格为文本》选择性粘贴》值8 T; V, V% e4 O' }! W
选中》表格转换为文本》粘贴》分列》对分列选项设置为文本
: U5 {9 F5 D1 {( o$ n) N 另存为文本文件》EXCEL中打开文本文件》对导入文本对话框进行对应设置5 @. v; G0 x6 T" P9 d$ k
) q/ n/ S: @) [& c U; u; N中国会计社区是国内最知名、最火爆的会计论坛.中国会计网（www.canet.com.cn)主办26、如果隐藏了B列，如果让它显示出来？
. N! j( K2 ^, b& N1 U s/ c: y中国会计社区选中A到C列，点击右键，取消隐藏
; O/ n* P2 Z% {4 I# x选中A到C列，双击选中任一列宽线或改变任一列宽
" `9 W* k& u; ^, Z4 a: g中国会计社区将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。 |
|
楼主: duoxiai
