比较常用的25条Excel技巧

zcc

1、两列数据查找相同值对应的位置 & z, J! i& n# Y& ?" l" J4 N　　=MATCH(B1,A:A，0) 6 t( d$ d; a8 I0 |2、已知公式得结果 4 K9 y3 @: e2 Z8 Y: M1 B( H) v9 W% R　　定义名称=EVALUATE(Sheet1!C1) 1 E$ g/ W+ D9 r( X. ` . e# X+ l$ D; `* _1 u　　已知结果得公式 2 }" E# z% r! u3 {/ ~" m % \. S+ Q# F/ ?) K　　定义名称=GET.CELL(6,Sheet1!C1) 3、强制换行 - w8 `/ T8 m/ j d- V/ f 　　用Alt+Enter 4、超过15位数字输入 　　这个问题问的人太多了，也收起来吧。一、单元格设置为文本;二、在输入数字前先输入' 1 j! K. h$ X) U5 {7 J& s 2 h8 a$ Q1 `+ a# K" t" w( i- z5、如果隐藏了B列，如果让它显示出来? 　　选中A到C列，点击右键，取消隐藏 ) F8 P0 w* A, q : d. U) g' q6 y! ]8 `3 y/ l+ S　　选中A到C列，双击选中任一列宽线或改变任一列宽 9 n9 B0 o. ^* {3 K2 R* ?& k　　将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。 ( U* |9 z8 @% h2 V) i - U3 O$ s7 @# }# g& _* T6、EXCEL中行列互换 ( [3 l# f7 X4 l" l, c　　复制，选择性粘贴，选中转置，确定即可 / w# O' A' Q! i7、Excel是怎么加密的 % U# K2 D; K @3 M! P2 i 　　（1）、保存时可以的另存为>>右上角的"工具">>常规>>设置 2 p$ u7 c% m- @- T# w6 M0 B 　　（2）、工具>>选项>>安全性 8、关于COUNTIF 　　COUNTIF函数只能有一个条件，如大于90，为=COUNTIF(A1:A10,">=90") 　　介于80与90之间需用减，为 =COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90") 9、根据身份证号提取出生日期 0 s) @2 [& z A0 N- U 　　（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)),"错误身份证号")) 3 ?0 t4 c0 s2 w3 x: h5 b　　（2）、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1 / e, i/ A+ U A. S9 ~ 10、想在SHEET2中完全引用SHEET1输入的数据 * X, z2 I' P& }4 a- G0 P + ~0 C2 T! U" T7 ?: k# b- F8 X　　工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。 7 z+ H8 d) b. b 6 n0 E2 G- w' b6 K/ j11、一列中不输入重复数字 　　[数据]--[有效性]--[自定义]--[公式] N. G3 B* ?1 N5 D- m / \% y, c1 d @# d0 I　　输入=COUNTIF(A:A,A1)=1 　　如果要查找重复输入的数字 　　条件格式》公式》=COUNTIF(A:A,A5)>1》格式选红色 $ y) d1 k) d* O5 r% r 7 v6 s& [& P' |12、直接打开一个电子表格文件的时候打不开 4 x$ P' }+ u. q0 r* a# ] 　　“文件夹选项”-“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%,如果没有，请手工加上 + B: Q9 [1 J: F$ Z! m& ]13、excel下拉菜单的实现 1 o; j* _' \! ^# Q8 D　　[数据]-[有效性]-[序列] 14、 10列数据合计成一列 　　=SUM(OFFSET($A$1,(ROW()-2)*10+1,,10,1)) 9 A3 {- C% U& K15、查找数据公式两个(基本查找函数为VLOOKUP,MATCH) Y$ _; Q' U/ ^8 }　　（1）、根据符合行列两个条件查找对应结果 . @2 t6 h2 q5 p " ^+ S( o& a4 r0 N% W　　=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE) 1 E) t- w9 p; l( F) W" |. ?　　（2）、根据符合两列数据查找对应结果(为数组公式) # Z j* X6 [: h3 j9 E ! D6 G4 W& W/ Z9 N0 G　　=INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0)) % _. r: A6 N- l2 n% V/ ~16、如何隐藏单元格中的0 　　单元格格式自定义0;-0;;@ 或 选项》视图》零值去勾。呵呵，如果用公式就要看情况了。 17、多个工作表的单元格合并计算 6 n7 b v! {9 } 　　=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4) 18、获得工作表名称 3 j) j/ E' [9 t; ` & g: `5 \( M! h2 V　　（1）、定义名称：Name ; j- }, |- r& T# Y! W) b　　=GET.DOCUMENT(88) / J7 J5 n/ l3 L% P j 　　（2）、定义名称：Path 　　=GET.DOCUMENT(2) 　　（3）、在A1中输入=CELL("filename")得到路径级文件名 8 q, l* A0 g/ x$ ~' y 　　在需要得到文件名的单元格输入 7 K! r) |7 H' h0 T1 i 8 v8 K- w; e$ @4 ]& ]　　=MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1)) 　　（4）、自定义函数 2 h9 `$ b% r8 p( M! a 　　Public Function name() & q3 {/ F9 C5 [ 　　Dim filename As String 0 {+ i& S7 B& d) [5 E! r' P 　　filename = ActiveWorkbook.name - y1 O( k; |5 ^" p M! @　　name = filename 　　End Function 19、如何获取一个月的最大天数 + X/ V' w5 v* q6 O' Q2 N/ ]2 | 　　:"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1为"2001-03-01 6 X* e9 M( e" Y# N6 b1 s2 S6 L - i! f( `, C2 h p$ t) x9 U　　数据区包含某一字符的项的总和,该用什么公式 ) p+ E. q" e6 W* L" q9 X- E2 Q' u 9 f% N0 y: c7 S3 a; o　　=sumif(a:a,"*"&"某一字符"&"*",数据区) ; |) b0 z. R. o! N3 X2 p　　最后一行为文本： 1 L2 O/ L5 d2 B6 j: ~! g. E: L. N2 W5 m8 \　　=offset($b$1,MATCH(CHAR(65535),b:b)-1,) 　　最后一行为数字： & B4 k0 e6 t, k$ |6 v2 u$ s 　　=offset($b$1,MATCH(9.9999E+307,b:b)-1,) 0 c, i2 y c& j+ j* Y & z+ _5 r4 c& g0 ~7 r! B; S: V　　或者：=lookup(2,1/(b1:b1000<>""),b1:b1000) " f$ \ s* k5 A2 p0 ?' c 　　评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分?同时显示出被去掉的分数。 ( y) C4 [& m2 {+ ^$ z) c3 W ( z5 A# ~7 x/ V. J$ { ]　　看看trimmean()函数帮助。 : X- v: w3 \. }+ z. t* Z( w4 J9 L; A　　被去掉的分数： % U# {4 `* b/ t1 Q- N: R 　　最大两个：=large(data,{1;2}) : d) m3 e8 @: d5 p4 z% U& e + _) g; `2 \" k　　最小两个：=small(data,{1;2}) 　　怎样很简单的判断最后一位是字母 　　right(a1)*1 / w/ Z; v! F- p% o7 Q 0 E {: j& k" y$ m3 M$ p3 {- C5 K　　出错的字母 ; s$ j7 t1 n) w2 g- n" c 5 l& |( k) b5 D, G' D8 }　　=IF(ISNUMBER(--RIGHT(A1,1)),"数字","字母") 5 z+ `4 k. M% J 　　=IF(ISERR(RIGHT(A1)*1),"字母","数字") 8 V& L* X2 ^0 G2 }　　如何 设置单元格，令其不接受包含空格的字符 , [5 G: P* U* D0 S* U　　选定A列 　　数据——有效性——自定义——公式 　　=iserror(find(" ",a1)) 　　数据--有效性--自定义--公式 ( ?& h3 e& K8 ? , V" I! g6 Z; B4 w. |. t　　=len(a1)=len(trim(a1)) , l0 G+ E0 Z& S　　原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22..... 　　现在是=sum(n(offset(a1,(row(1:10)-1)*3,))) 　　在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据我输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用? / Y# O" z; U! _1 p ( @4 Y, E1 P0 r6 Q) b　　=INDIRECT("A1"&"!"&"E1") A1为工作表名 " X8 I4 R4 n Z1 i- Y L3 I+ [ \- U2 B. u　　奇数行求和 =SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2)) " ~! o" o4 o( l& r# E' h( P　　偶数行求和 =SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2))) 9 r- A2 k" I) P* ^ & M ~3 N; [% G+ c' s, ^% H　　查看字符串字数 ( |* p+ k9 p9 p" r( { 　　=LEN(A1) ! m+ [9 Y; y, m2 L 　　求非空单元格数量 8 ~$ c6 Y5 t2 W6 D. B U* n) Z 　　公式计算出来的数据，COUNTA不能用的(否则空字符也计算进去了) 　　=COUNTIF($E$3E$65536,"?*") ! [' V9 \: w9 r8 W) q% k7 R　　动态求和公式,自A列A1单元格到当前行前面一行的单元格求和. 　　=SUM(INDIRECT("A1:A"&ROW()-1)) ) R, b: ]# v7 x t& \5 L1 ~ / b) H5 H5 I; h9 @# @/ s) M7 o20、比较好用的EXCEL文档修复工具 9 Z) [+ H7 Q7 M" N4 [ ' U- L' A- e" k9 U; E5 ~　　ExcelRecovery 7 b% y9 H% s1 B; M # p, `& z9 q* s1 _! {21、EXCEL开方运算 9 v# G9 K1 f9 n: @# D3 m9 |　　将8开3次方，可以用这个公式，在单元格中输入　　=8^(1/3) ! P/ D; k& t* C, b# ^! @ 22、单元格中的数据分散对齐 , U5 C) |- G8 {+ N, ^ / G& J* h3 o% P) ]! r; W0 m　　文本格式》全角输入 0 K1 D2 A: }( V) F 23、查找工作表中的链接 , K, x( D8 Z' A0 F/ a1 q% N 　　Ctrl+~ 或编辑》链接 / F9 Q$ @9 d& z8 A* Y 24、如何让空单元格自动填为0 3 V$ B; @' Z( E# O7 g8 C; M6 s% s 　　选中需更改的区域》查找》空》替换》0 $ X0 F1 y% e2 @1 C& n1 j5 W; _$ P) h25、把Word里的数字转换到Excel 　　方法有多种，选中》复制》设置输入单元格为文本》选择性粘贴》值 　　选中》表格转换为文本》粘贴》分列》对分列选项设置为文本 # b1 s1 u* i. z/ x% o: T w0 B　　另存为文本文件》EXCEL中打开文本文件》对导入文本对话框进行对应设置

zwrjt

不错

seven812503

非常有用，谢谢共享！~

自由人物

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

rixmirror

好。