比较常用的25条Excel技巧

zcc

1、两列数据查找相同值对应的位置 　　=MATCH(B1,A:A，0) 9 |+ n; ^$ t; j; O' ~; S3 r( P- _ 2、已知公式得结果 0 Z5 X: S3 \# f: f% l( l; B　　定义名称=EVALUATE(Sheet1!C1) ! ^: D4 U3 k* U2 w/ |( {6 G 　　已知结果得公式 　　定义名称=GET.CELL(6,Sheet1!C1) ( ]3 ?' e X5 s s/ u0 ? 8 b' t; }; C$ \3、强制换行 　　用Alt+Enter 4、超过15位数字输入 / K2 h; L' t) D' j" h( z 　　这个问题问的人太多了，也收起来吧。一、单元格设置为文本;二、在输入数字前先输入' 0 }7 Q* r& p0 d7 D( c# S2 S 5、如果隐藏了B列，如果让它显示出来? 　　选中A到C列，点击右键，取消隐藏 ) B% m' i9 {2 ]; z5 L/ L. S 　　选中A到C列，双击选中任一列宽线或改变任一列宽 0 }- d; M6 z x 　　将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。 ! L/ ^+ L; d+ `5 G9 f 6、EXCEL中行列互换 5 n$ p" j" C" y9 S 　　复制，选择性粘贴，选中转置，确定即可 ! |' g3 H& | `$ u' x" f) k8 E7、Excel是怎么加密的 　　（1）、保存时可以的另存为>>右上角的"工具">>常规>>设置 ' {0 K% n q, t/ K: ~ 　　（2）、工具>>选项>>安全性 8、关于COUNTIF ) T, T$ F3 y* k3 W9 T1 R　　COUNTIF函数只能有一个条件，如大于90，为=COUNTIF(A1:A10,">=90") + n3 M2 c! i1 x! l/ x 　　介于80与90之间需用减，为 =COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90") + U$ p8 t. ]3 u; ^; s( z; N 9、根据身份证号提取出生日期 % l, J7 c. t( c　　（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）、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1 8 }7 g0 |" @, `$ j) a* e9 X $ R6 I7 h! K ]5 k$ J% l10、想在SHEET2中完全引用SHEET1输入的数据 " p7 o9 k2 E5 ~" C# Q 2 S1 I" D: Q4 G4 a: |- x! t7 z4 U7 _　　工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。 ; E+ Y, y: ~/ T5 L2 \ 11、一列中不输入重复数字 　　[数据]--[有效性]--[自定义]--[公式] " |4 m) A6 Z, s 　　输入=COUNTIF(A:A,A1)=1 5 |& W1 M& J( X$ p5 W: R ) R+ {7 {1 v+ h4 p, s( Q1 F0 z　　如果要查找重复输入的数字 " Z$ {/ p5 g/ u　　条件格式》公式》=COUNTIF(A:A,A5)>1》格式选红色 7 A2 a, s3 z' ^/ U9 S 12、直接打开一个电子表格文件的时候打不开 * S2 B9 I: |! v) `9 X8 b$ }& [ ; J& g7 B/ Q# c7 w% y% f9 N3 s　　“文件夹选项”-“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%,如果没有，请手工加上 & A$ p( d. i( |% t" }* ?8 `. v% w13、excel下拉菜单的实现 - M5 l" k, s' o2 T1 }& v # v! { s. p, f8 m6 L/ @& S　　[数据]-[有效性]-[序列] ! z; ?% V: k- j. d7 S( H7 n14、 10列数据合计成一列 . ?0 K1 Q% P7 W4 x1 ]4 E 　　=SUM(OFFSET($A$1,(ROW()-2)*10+1,,10,1)) 15、查找数据公式两个(基本查找函数为VLOOKUP,MATCH) ) h5 Z/ t) o) e: V; j6 S: H, j 　　（1）、根据符合行列两个条件查找对应结果 5 Q$ H) w2 _ E. A5 n; i2 O D' V5 `. I 　　=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE) 7 f# e. K/ v4 E( ~7 U/ V 　　（2）、根据符合两列数据查找对应结果(为数组公式) 　　=INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0)) 16、如何隐藏单元格中的0 　　单元格格式自定义0;-0;;@ 或 选项》视图》零值去勾。呵呵，如果用公式就要看情况了。 17、多个工作表的单元格合并计算 - h S0 M% y% i% t " |" b0 Z# |8 B2 p　　=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4) % Q: S. p6 q w6 r8 K18、获得工作表名称 　　（1）、定义名称：Name 1 F( u7 _7 D" i 　　=GET.DOCUMENT(88) * V, _1 E$ I+ F# A　　（2）、定义名称：Path 　　=GET.DOCUMENT(2) 0 L: p1 C$ h3 a8 u" C$ z1 M- }　　（3）、在A1中输入=CELL("filename")得到路径级文件名 $ o1 k/ ^9 T! t* \ ) A& y) B7 r+ ?& x- T& \ ~　　在需要得到文件名的单元格输入 2 W& }9 b3 l9 `% ]: h9 a9 G　　=MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1)) : A" O1 I( I6 ^8 Y" Q S' E& u8 ~　　（4）、自定义函数 1 c+ n z" |0 y% h- t8 A3 a, t! z8 F( J& J 2 y& |& L/ g) R5 |　　Public Function name() & x; i3 d1 F# X2 ^; v# X! C　　Dim filename As String # o) k. j* H d& ^　　filename = ActiveWorkbook.name `: P5 D3 |! h- e3 s3 q( [ 　　name = filename & n0 I# j. h. F& O 　　End Function / e7 c3 B9 ?4 O1 x! j2 ? $ o' a" n4 k, e/ w( Z" L% S19、如何获取一个月的最大天数 ( b) g& V; t4 x6 p) h6 m, r　　:"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1为"2001-03-01 ) l# h* z, C3 n/ R* A. f% _. M1 i　　数据区包含某一字符的项的总和,该用什么公式 9 a! W% T7 X ^& C 　　=sumif(a:a,"*"&"某一字符"&"*",数据区) " `& A7 H; e* o( ]9 A1 K+ [' T - ~! o4 p+ s: K: e' L" F" R ^　　最后一行为文本： ' N# ~9 d$ ~0 z4 V' @% R - ~# M, A* N' X　　=offset($b$1,MATCH(CHAR(65535),b:b)-1,) : w( W6 K- P0 T 　　最后一行为数字： 　　=offset($b$1,MATCH(9.9999E+307,b:b)-1,) 　　或者：=lookup(2,1/(b1:b1000<>""),b1:b1000) 　　评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分?同时显示出被去掉的分数。 7 J+ U( R; b+ ], q: t! g: | 　　看看trimmean()函数帮助。 & t- L3 e; I+ `% b& I" O 　　被去掉的分数： ! I5 ~! G7 O3 g( Y% T 　　最大两个：=large(data,{1;2}) & P% o g" R. _　　最小两个：=small(data,{1;2}) 　　怎样很简单的判断最后一位是字母 ; _9 V9 o8 J* p# u' a3 Z3 W & b& r4 W( T& h1 {" O6 S( t　　right(a1)*1 ( u/ [9 T9 f" W% ^; y0 S' b- A0 ~: I v 　　出错的字母 7 ?! v0 A! ^( s3 t" q- S( i8 W　　=IF(ISNUMBER(--RIGHT(A1,1)),"数字","字母") : S6 S3 B& J7 u ]! a 　　=IF(ISERR(RIGHT(A1)*1),"字母","数字") 　　如何 设置单元格，令其不接受包含空格的字符 $ V0 O0 z" r6 I. g) O, ^6 F/ g 6 l9 P' {2 p9 P+ z# G8 ~+ I3 s' p　　选定A列 , |* ~- r, E/ s4 Z- N5 i　　数据——有效性——自定义——公式 " x- s' m- W( j3 j4 E* \ 　　=iserror(find(" ",a1)) + f. n. e" d" Z( g# w　　数据--有效性--自定义--公式 　　=len(a1)=len(trim(a1)) 　　原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22..... 　　现在是=sum(n(offset(a1,(row(1:10)-1)*3,))) 7 G" ^! q9 }' l k 9 @, `- ^/ w1 i* O+ E) d　　在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据我输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用? + Q, u! r: A3 {; L 4 g' [# L1 Z- M% o% [3 ~& d' t/ ]- o　　=INDIRECT("A1"&"!"&"E1") A1为工作表名 / k8 u2 S) l; T7 k0 y+ ? 　　奇数行求和 =SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2)) 　　偶数行求和 =SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2))) / o& S+ T f- l' L7 l; O 　　查看字符串字数 　　=LEN(A1) $ T1 {, M8 N( k3 J1 U 　　求非空单元格数量 & r4 C1 n# z* O: V( _ ; _3 ]8 h* r, \ d& A　　公式计算出来的数据，COUNTA不能用的(否则空字符也计算进去了) 　　=COUNTIF($E$3E$65536,"?*") 　　动态求和公式,自A列A1单元格到当前行前面一行的单元格求和. 4 Z8 X/ l3 q- e2 ~ + h0 @0 k$ q' N　　=SUM(INDIRECT("A1:A"&ROW()-1)) 8 M+ V7 _! w3 m/ m1 v0 G 8 B& b+ T8 [ P* j+ o1 n20、比较好用的EXCEL文档修复工具 　　ExcelRecovery 21、EXCEL开方运算 G J. }$ L& i" h V+ J( b7 d 　　将8开3次方，可以用这个公式，在单元格中输入　　=8^(1/3) . q+ ^+ [+ \2 S2 X3 F! f2 z8 m- Y) Q( N2 ]) n 22、单元格中的数据分散对齐 + ~7 A0 ~3 E I" `" ?& G * p) ]. s9 K3 X: E' O; G7 s4 U　　文本格式》全角输入 & O! `7 @" {4 t: Y6 L23、查找工作表中的链接 　　Ctrl+~ 或编辑》链接 2 c# o% M7 o7 M8 ~* f4 V 24、如何让空单元格自动填为0 ' I5 M( I" P$ Q! t * E4 @6 R% D$ D　　选中需更改的区域》查找》空》替换》0 1 \! A* }1 P. Q! b- \1 `$ ?9 K/ O / L( M5 v6 v& k5 ]: L* V25、把Word里的数字转换到Excel 　　方法有多种，选中》复制》设置输入单元格为文本》选择性粘贴》值 5 u: m, H- A& n: P! F* f 　　选中》表格转换为文本》粘贴》分列》对分列选项设置为文本 6 H2 E. m) H1 l* y" Q! ~ 7 p g* ^2 e" ]6 K" {　　另存为文本文件》EXCEL中打开文本文件》对导入文本对话框进行对应设置

zwrjt

不错

seven812503

非常有用，谢谢共享！~

自由人物

保存下来，以后统计就方便多了！

rixmirror

好。