比较常用的25条Excel技巧

zcc

1、两列数据查找相同值对应的位置 　　=MATCH(B1,A:A，0) 2、已知公式得结果 6 e8 v% L# }% ~- K# d& F; Q 　　定义名称=EVALUATE(Sheet1!C1) - J4 F% W9 B6 C! u 6 B1 L, `4 M9 t( h　　已知结果得公式 * z3 d H/ I7 q' ?6 S 3 f4 e% s7 \( K+ D0 U　　定义名称=GET.CELL(6,Sheet1!C1) $ C* N P, ]! Z, N2 R, u 3、强制换行 ) u c+ L7 p/ m3 t& N　　用Alt+Enter & o3 {8 j" b& f; V7 B ' g/ J) N+ T L$ _. W! I) T/ ^4、超过15位数字输入 6 ^2 g; @6 h5 M8 Y9 V 　　这个问题问的人太多了，也收起来吧。一、单元格设置为文本;二、在输入数字前先输入' , C! P, U+ g* F$ O5、如果隐藏了B列，如果让它显示出来? + E4 n4 Z7 N$ d g( ` : [5 z$ M9 ?% L- ~　　选中A到C列，点击右键，取消隐藏 8 f6 C) a, |/ V; T　　选中A到C列，双击选中任一列宽线或改变任一列宽 　　将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。 6、EXCEL中行列互换 　　复制，选择性粘贴，选中转置，确定即可 + r- b2 F2 o/ [9 T0 I8 p 7、Excel是怎么加密的 0 H0 r: g9 |# H: B 　　（1）、保存时可以的另存为>>右上角的"工具">>常规>>设置 8 y. ?) y6 V( ]$ x8 Q1 F! V ' S2 v2 \, x2 X8 H) t. T　　（2）、工具>>选项>>安全性 , ~4 ^* c; d4 D5 v1 l& j v + q4 f1 }! ^+ L; H! F3 b8、关于COUNTIF 　　COUNTIF函数只能有一个条件，如大于90，为=COUNTIF(A1:A10,">=90") _% y; ~( |0 I6 \4 m　　介于80与90之间需用减，为 =COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90") . a, J6 h0 ~9 @. n; |2 D j; Q + y. N$ E* ~$ ~ k' M& Q% P9、根据身份证号提取出生日期 - @. l1 |9 i) s9 o　　（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)),"错误身份证号")) ) B. ?8 X3 m! ~' w& s1 p　　（2）、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1 1 A4 i. M7 y; s( g/ n+ v + ?8 u( `* c1 Y7 A; k+ ^, a10、想在SHEET2中完全引用SHEET1输入的数据 ; {6 ], l" f( i5 S8 a1 v ! ~, J! Y7 e+ I$ h8 b& a　　工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。 : o* _& N @- g: X11、一列中不输入重复数字 0 _5 k, P& B* W5 b. e # s! [. M0 I+ t5 Q/ |　　[数据]--[有效性]--[自定义]--[公式] 　　输入=COUNTIF(A:A,A1)=1 * ]( X( m V5 ~% `9 |　　如果要查找重复输入的数字 # t# T; u8 D5 V8 U0 i% J" f D　　条件格式》公式》=COUNTIF(A:A,A5)>1》格式选红色 * n# _! o3 e0 p7 P( M0 I: S 12、直接打开一个电子表格文件的时候打不开 7 N/ K5 o+ X' j; y# w2 R! F　　“文件夹选项”-“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%,如果没有，请手工加上 13、excel下拉菜单的实现 3 q1 I9 v. e2 G6 {! c5 U 　　[数据]-[有效性]-[序列] : t, L' V) d( H! M* w14、 10列数据合计成一列 　　=SUM(OFFSET($A$1,(ROW()-2)*10+1,,10,1)) 15、查找数据公式两个(基本查找函数为VLOOKUP,MATCH) ' ?$ K U a- |+ ?% h- s0 o) H$ W　　（1）、根据符合行列两个条件查找对应结果 3 z$ l* ]4 c# F @3 j　　=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE) ( S+ ^6 |- \$ k0 N8 m6 F　　（2）、根据符合两列数据查找对应结果(为数组公式) 8 s1 e5 E* I9 x　　=INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0)) . [ T* O7 K! b6 D: n16、如何隐藏单元格中的0 * n' y+ o8 C* O- p# s( P 　　单元格格式自定义0;-0;;@ 或 选项》视图》零值去勾。呵呵，如果用公式就要看情况了。 # V( u1 I3 b$ Y" M2 u l) ]/ N 4 w# y1 X0 x2 Y$ p" X17、多个工作表的单元格合并计算 0 ~, y& t! T; H# G 　　=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4) * s+ y7 N4 w6 a" T3 x, F$ z1 n18、获得工作表名称 9 E9 t: A$ ? I: E 　　（1）、定义名称：Name 0 l$ D7 ~1 g8 k5 i 　　=GET.DOCUMENT(88) ; }& C# k& S! e4 Y0 t　　（2）、定义名称：Path * O) Z/ {3 R) s0 a8 p4 A: Y1 s/ a& B % P# j) Z$ k3 R' _" P; o. j% h/ b　　=GET.DOCUMENT(2) 　　（3）、在A1中输入=CELL("filename")得到路径级文件名 2 l4 Q @# h$ U9 h 　　在需要得到文件名的单元格输入 & m, Q1 A, H- F" k- F　　=MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1)) ) n* c" B5 C3 r# q/ S　　（4）、自定义函数 # e' T2 j9 |( z) Z& o 　　Public Function name() 9 c9 h/ [$ J7 I1 k. W+ L: t　　Dim filename As String ! T, q3 }$ t4 l 　　filename = ActiveWorkbook.name ) O- O( O. c) w 　　name = filename + ~+ s9 ^, y5 j% @ ' N. D( ~) {7 {' I( w　　End Function 0 e& w' P3 A# ^& i3 l2 F 3 a! _, o+ t$ c/ C) Y# T! M19、如何获取一个月的最大天数 　　:"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1为"2001-03-01 $ j/ X& L3 A" a3 \. X8 a( C/ K" X 　　数据区包含某一字符的项的总和,该用什么公式 # n9 T8 i8 }' t$ K! x 0 m* i- R8 L( A2 Q5 o( s( w$ J　　=sumif(a:a,"*"&"某一字符"&"*",数据区) . N* O, O; T$ h% F( ?; w& L 8 x" @" l/ x/ `0 H　　最后一行为文本： * f7 Q9 E' S! f1 k 0 s, W9 z( A. d& g　　=offset($b$1,MATCH(CHAR(65535),b:b)-1,) 　　最后一行为数字： 3 ~$ \, x. R# K2 B 　　=offset($b$1,MATCH(9.9999E+307,b:b)-1,) 1 d6 |2 c7 t# ] 8 \1 j3 O( u8 U- b9 r9 ^/ A: q　　或者：=lookup(2,1/(b1:b1000<>""),b1:b1000) . V9 R& j1 @& Q) l' V% H : b8 j7 `- _/ w2 a; u% T　　评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分?同时显示出被去掉的分数。 　　看看trimmean()函数帮助。 4 B# Z E2 ^& \/ J7 b% L+ v0 S , c. W9 v3 d8 f- i4 H( K" ]　　被去掉的分数： 　　最大两个：=large(data,{1;2}) , \+ @8 } k5 G! q& {( K6 K 　　最小两个：=small(data,{1;2}) - A/ ]4 A$ M9 H# E- W5 J1 Z, @% q4 a　　怎样很简单的判断最后一位是字母 0 m/ a9 ~! A8 V7 K: C7 Y, a" Y 　　right(a1)*1 y& z. P/ m: I) d y/ |　　出错的字母 / C& ^, q/ F" K+ L' @ 　　=IF(ISNUMBER(--RIGHT(A1,1)),"数字","字母") / C% X; d! M% a8 r* C" h 　　=IF(ISERR(RIGHT(A1)*1),"字母","数字") ( O% ^# E) c/ @6 `( t; b/ v 　　如何 设置单元格，令其不接受包含空格的字符 　　选定A列 　　数据——有效性——自定义——公式 ) b1 _! |2 z5 K& J- h( k: y7 E+ V 　　=iserror(find(" ",a1)) 　　数据--有效性--自定义--公式 　　=len(a1)=len(trim(a1)) % v: q8 `' `3 P- z! Q $ \. u4 N; z/ J1 K3 ~9 ~; ]: V　　原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22..... 　　现在是=sum(n(offset(a1,(row(1:10)-1)*3,))) ! V7 R1 E5 R' P- d' n' `; l　　在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据我输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用? 5 ~* ]3 `1 A; z! k( g 　　=INDIRECT("A1"&"!"&"E1") A1为工作表名 * A/ L n. d b. x9 Y( Z　　奇数行求和 =SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2)) % C B- e% S$ {5 X8 I 　　偶数行求和 =SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2))) 　　查看字符串字数 9 K7 k7 U. v& B' b# e　　=LEN(A1) 3 s& p# ]8 a, T% W( t 　　求非空单元格数量 + u' N! K, u! }* k: |& U 3 O1 P" |7 r$ @ M　　公式计算出来的数据，COUNTA不能用的(否则空字符也计算进去了) ' L* Z$ ^/ u- Y. G　　=COUNTIF($E$3E$65536,"?*") ) c, R- Y! N( H8 Y; O0 Q　　动态求和公式,自A列A1单元格到当前行前面一行的单元格求和. 　　=SUM(INDIRECT("A1:A"&ROW()-1)) : X1 G" j/ U# x" p4 @0 Z6 i9 c 20、比较好用的EXCEL文档修复工具 . ?! n& ?, }( M* _# X3 W* f/ |　　ExcelRecovery 3 X s& l5 g, I 21、EXCEL开方运算 7 }; B* U: i% H9 I! [; m: h' y 　　将8开3次方，可以用这个公式，在单元格中输入　　=8^(1/3) ' I' s- i$ z: x7 w6 c" {' s* O0 } 22、单元格中的数据分散对齐 　　文本格式》全角输入 23、查找工作表中的链接 , l5 |& o1 O1 z8 a9 w7 B E6 c 6 n5 M# b. c! K! @( a　　Ctrl+~ 或编辑》链接 7 M4 ~2 I8 X9 g \* X 24、如何让空单元格自动填为0 % j8 t- b3 D* S- W　　选中需更改的区域》查找》空》替换》0 25、把Word里的数字转换到Excel # {! Y0 t8 Z: u 　　方法有多种，选中》复制》设置输入单元格为文本》选择性粘贴》值 + U" L4 K T- z1 Z& P2 l$ Y　　选中》表格转换为文本》粘贴》分列》对分列选项设置为文本 . f; d( c- j( d+ p 　　另存为文本文件》EXCEL中打开文本文件》对导入文本对话框进行对应设置

zwrjt

不错

seven812503

非常有用，谢谢共享！~

自由人物

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

rixmirror

好。